英文:
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'
)
)
)
这样可以避免OuterRef
在FilteredRelation
内部被处理。
英文:
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
.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论