如何使用OuterRef与FilteredRelation?

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

How to use FilteredRelation with OuterRef?

问题

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

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

subquery = ArraySubquery(
    LineOutlierReport.objects
    .annotate(
        filtered_relation=FilteredRelation('lineoutlier', 
            condition=Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True)
        )
    )
    .filter(Q(id__in=last_5_reports_ids))
    .values(
        json=JSONObject(
            severity='filtered_relation__severity',
            report_id='id',
            report_start_date='start_date',
            report_end_date='end_date'
        )
    )
)

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

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

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

subquery = Subquery(
    LineOutlierReport.objects
    .filter(Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True))
    .filter(Q(id__in=last_5_reports_ids))
    .values(
        json=JSONObject(
            severity=OuterRef('lineoutlier__severity'),
            report_id=OuterRef('id'),
            report_start_date=OuterRef('start_date'),
            report_end_date=OuterRef('end_date')
        )
    )
)

queryset = LineOutlier.objects.filter(report=self.kwargs['report_pk'], report__apn__customer__cen_id=self.kwargs['customer_cen_id']) \
    .select_related('category__traffic') \
    .select_related('category__frequency') \
    .select_related('category__stability') \
    .prefetch_related('category__traffic__labels') \
    .prefetch_related('category__frequency__labels') \
    .prefetch_related('category__stability__labels') \
    .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

queryset = LineOutlier.objects.filter(report=self.kwargs['report_pk'], report__apn__customer__cen_id=self.kwargs['customer_cen_id']) \
                    .select_related('category__traffic') \
                    .select_related('category__frequency') \
                    .select_related('category__stability') \
                    .prefetch_related('category__traffic__labels') \
                    .prefetch_related('category__frequency__labels') \
                    .prefetch_related('category__stability__labels') \
                    .annotate(history=subquery)

The subquery

subquery = ArraySubquery(
            LineOutlierReport.objects
            .filter((Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True)) & Q(id__in=last_5_reports_ids))
             .values(json=JSONObject(
                            severity='lineoutlier__severity',
                            report_id='id',
                            report_start_date='start_date',
                            report_end_date='end_date'
                            )
                    )
        )

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

SQL Generated

SELECT "mlformalima_lineoutlier"."id",
       "mlformalima_lineoutlier"."imsi",
       ARRAY(
        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"
          FROM "mlformalima_lineoutlierreport" V0
          LEFT OUTER JOIN "mlformalima_lineoutlier" V1
            ON (V0."id" = V1."report_id")
         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))
       ) AS "history",
  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

SELECT "mlformalima_lineoutlier"."id",
       "mlformalima_lineoutlier"."imsi",
       ARRAY(
        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"
          FROM "mlformalima_lineoutlierreport" V0
          LEFT OUTER JOIN "mlformalima_lineoutlier" V1
            ON (V0."id" = V1."report_id" AND ((V1."id" IS NULL) OR V1."imsi" = ("mlformalima_lineoutlier"."imsi")))
         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))
       ) AS "history",
  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

subquery = ArraySubquery(
            LineOutlierReport.objects
            .annotate(filtered_relation=FilteredRelation('lineoutlier', condition=Q(lineoutlier__imsi=OuterRef('imsi')) | Q(lineoutlier__isnull=True)))
            .filter(Q(id__in=last_5_reports_ids))
            .values(json=JSONObject(
                            severity='filtered_relation__severity',
                            report_id='id',
                            report_start_date='start_date',
                            report_end_date='end_date'
                            )
                    )
        )

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中引用它,就像这样:

subquery = ArraySubquery(
    LineOutlierReport.objects
    .annotate(
        outer_imsi=OuterRef('imsi'),
        filtered_relation=FilteredRelation('lineoutlier', condition=Q(lineoutlier__imsi=F('outer_imsi')) | Q(lineoutlier__isnull=True)))
    .filter(Q(id__in=last_5_reports_ids))
    .values(json=JSONObject(
            severity='filtered_relation__severity',
            report_id='id',
            report_start_date='start_date',
            report_end_date='end_date'
        )
    )
)

这样可以避免OuterRefFilteredRelation内部被处理。

英文:

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

subquery = ArraySubquery(
		LineOutlierReport.objects
		.annotate(
			outer_imsi=OuterRef('imsi'),
			filtered_relation=FilteredRelation('lineoutlier', condition=Q(lineoutlier__imsi=F('outer_imsi')) | Q(lineoutlier__isnull=True)))
		.filter(Q(id__in=last_5_reports_ids))
		.values(json=JSONObject(
						severity='filtered_relation__severity',
						report_id='id',
						report_start_date='start_date',
						report_end_date='end_date'
						)
				)
	)

That way you avoid OuterRef being processed inside FilteredRelation.

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

发表评论

匿名网友

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

确定