如何使用OuterRef与FilteredRelation？

huangapple
go评论95阅读模式
英文:

How to use FilteredRelation with OuterRef?

问题

我正在尝试使用Django ORM生成一个查询集，并且我无法找到如何在FilteredRelation与连接条件中使用OuterRef的方法。

在Django中，您可以尝试使用FilteredRelation来更改JOIN条件，但似乎无法与OuterRef一起使用。

  1. subquery = ArraySubquery(
  2.     LineOutlierReport.objects
  3.     .annotate(
  4.         filtered_relation=FilteredRelation('lineoutlier', 
  5.             condition=Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True)
  6.         )
  7.     )
  8.     .filter(Q(id__in=last_5_reports_ids))
  9.     .values(
  10.         json=JSONObject(
  11.             severity='filtered_relation__severity',
  12.             report_id='id',
  13.             report_start_date='start_date',
  14.             report_end_date='end_date'
  15.         )
  16.     )
  17. )

但是，您无法执行此查询，因为会出现以下错误：

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

要解决这个问题，您可以尝试使用Subquery来重新构建子查询，然后在主查询中使用它，如下所示：

  1. subquery = Subquery(
  2.     LineOutlierReport.objects
  3.     .filter(Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True))
  4.     .filter(Q(id__in=last_5_reports_ids))
  5.     .values(
  6.         json=JSONObject(
  7.             severity=OuterRef('lineoutlier__severity'),
  8.             report_id=OuterRef('id'),
  9.             report_start_date=OuterRef('start_date'),
  10.             report_end_date=OuterRef('end_date')
  11.         )
  12.     )
  13. )
  15. queryset = LineOutlier.objects.filter(report=self.kwargs['report_pk'], report__apn__customer__cen_id=self.kwargs['customer_cen_id']) \
  16.     .select_related('category__traffic') \
  17.     .select_related('category__frequency') \
  18.     .select_related('category__stability') \
  19.     .prefetch_related('category__traffic__labels') \
  20.     .prefetch_related('category__frequency__labels') \
  21.     .prefetch_related('category__stability__labels') \
  22.     .annotate(history=subquery)

这将在主查询的annotate中使用Subquery，并且应该在连接条件中正确使用OuterRef。

英文:

I'm trying to use Django ORM to generate a queryset and I can't find how to use an OuterRef in the joining condition with a FilteredRelation.

What I have in Django

Main queryset

  1. queryset = LineOutlier.objects.filter(report=self.kwargs['report_pk'], report__apn__customer__cen_id=self.kwargs['customer_cen_id']) \
  2.                     .select_related('category__traffic') \
  3.                     .select_related('category__frequency') \
  4.                     .select_related('category__stability') \
  5.                     .prefetch_related('category__traffic__labels') \
  6.                     .prefetch_related('category__frequency__labels') \
  7.                     .prefetch_related('category__stability__labels') \
  8.                     .annotate(history=subquery)

The subquery

  1. subquery = ArraySubquery(
  2.             LineOutlierReport.objects
  3.             .filter((Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True)) & Q(id__in=last_5_reports_ids))
  4.              .values(json=JSONObject(
  5.                             severity='lineoutlier__severity',
  6.                             report_id='id',
  7.                             report_start_date='start_date',
  8.                             report_end_date='end_date'
  9.                             )
  10.                     )
  11.         )

The request can be executed, but the SQL generated is not exactly what I want :

SQL Generated

  1. SELECT "mlformalima_lineoutlier"."id",
  2.        "mlformalima_lineoutlier"."imsi",
  3.        ARRAY(
  4.         SELECT JSONB_BUILD_OBJECT('severity', V1."severity", 'report_id', V0."id", 'report_start_date', V0."start_date", 'report_end_date', V0."end_date") AS "json"
  5.           FROM "mlformalima_lineoutlierreport" V0
  6.           LEFT OUTER JOIN "mlformalima_lineoutlier" V1
  7.             ON (V0."id" = V1."report_id")
  8.          WHERE ((V1."imsi" = ("mlformalima_lineoutlier"."imsi") OR V1."id" IS NULL) AND V0."id" IN (SELECT DISTINCT ON (U0."id") U0."id" FROM "mlformalima_lineoutlierreport" U0 WHERE U0."apn_id" = 2 ORDER BY U0."id" ASC, U0."end_date" DESC LIMIT 5))
  9.        ) AS "history",
  10.   FROM "mlformalima_lineoutlier"

The problem here is that the OuterRef condition (V1."imsi" = ("mlformalima_lineoutlier"."imsi")) is done on the WHERE statement, and I want it to be on the JOIN statement

What I want in SQL

  1. SELECT "mlformalima_lineoutlier"."id",
  2.        "mlformalima_lineoutlier"."imsi",
  3.        ARRAY(
  4.         SELECT JSONB_BUILD_OBJECT('severity', V1."severity", 'report_id', V0."id", 'report_start_date', V0."start_date", 'report_end_date', V0."end_date") AS "json"
  5.           FROM "mlformalima_lineoutlierreport" V0
  6.           LEFT OUTER JOIN "mlformalima_lineoutlier" V1
  7.             ON (V0."id" = V1."report_id" AND ((V1."id" IS NULL) OR V1."imsi" = ("mlformalima_lineoutlier"."imsi")))
  8.          WHERE V0."id" IN (SELECT DISTINCT ON (U0."id") U0."id" FROM "mlformalima_lineoutlierreport" U0 WHERE U0."apn_id" = 2 ORDER BY U0."id" ASC, U0."end_date" DESC LIMIT 5))
  9.        ) AS "history",
  10.   FROM "mlformalima_lineoutlier"

What I tried in Django

I tried to use the FilteredRelation to change the JOIN condition, but I can't seem to use it in combination with an OuterRef

  1. subquery = ArraySubquery(
  2.             LineOutlierReport.objects
  3.             .annotate(filtered_relation=FilteredRelation('lineoutlier', condition=Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True)))
  4.             .filter(Q(id__in=last_5_reports_ids))
  5.             .values(json=JSONObject(
  6.                             severity='filtered_relation__severity',
  7.                             report_id='id',
  8.                             report_start_date='start_date',
  9.                             report_end_date='end_date'
  10.                             )
  11.                     )
  12.         )

I can't execute this query because of the following error

ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

How can I modify my query to make it work ?

答案1

得分: 1

这看起来像是Django的这个bug。作为一种解决方法，您可以注释另一个列并在FilteredRelation中引用它，就像这样：

  1. subquery = ArraySubquery(
  2.     LineOutlierReport.objects
  3.     .annotate(
  4.         outer_imsi=OuterRef('imsi'),
  5.         filtered_relation=FilteredRelation('lineoutlier', condition=Q(lineoutlier__imsi=F('outer_imsi')) | Q(lineoutlier__isnull=True)))
  6.     .filter(Q(id__in=last_5_reports_ids))
  7.     .values(json=JSONObject(
  8.             severity='filtered_relation__severity',
  9.             report_id='id',
  10.             report_start_date='start_date',
  11.             report_end_date='end_date'
  12.         )
  13.     )
  14. )

这样可以避免OuterRef在FilteredRelation内部被处理。

英文:

This looks like this Django bug. As a workaround you can annotate another column and reference it in the FilteredRelation, like so :

  1. subquery = ArraySubquery(
  2. 		LineOutlierReport.objects
  3. 		.annotate(
  4. 			outer_imsi=OuterRef('imsi'),
  5. 			filtered_relation=FilteredRelation('lineoutlier', condition=Q(lineoutlier__imsi=F('outer_imsi')) | Q(lineoutlier__isnull=True)))
  6. 		.filter(Q(id__in=last_5_reports_ids))
  7. 		.values(json=JSONObject(
  8. 						severity='filtered_relation__severity',
  9. 						report_id='id',
  10. 						report_start_date='start_date',
  11. 						report_end_date='end_date'
  12. 						)
  13. 				)
  14. 	)

That way you avoid OuterRef being processed inside FilteredRelation.

huangapple
  • 本文由 发表于 2023年2月6日 19:23:45
  • 转载请务必保留本文链接：https://go.coder-hub.com/75360667.html
  • django
  • django-orm
  • postgresql
  • python
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定