在 Django 子查询中，我可以引用“父"吗?询问? [英] In a Django subquery, can I reference the "parent" query?

问题描述

在 Django ORM 中创建子查询很简单(只需使用 QuerySet 作为另一个查询的一部分)，但是该子查询是否可以引用父"(外部，主)中的字段查询?

It's simple to create subqueries in Django ORM (just use a QuerySet as part of another query), but is it possible for that subquery to reference fields in the "parent" (outer, main) query?

有关我要实现的目标的完整示例，请参阅此工作 SQL Fiddle.我把它分解成两个问题(另一个问题).在这种情况下，我有一个模型 Whole 表示必须达到的值.几个 Part 使用自己的(计算的)值对其做出贡献.我想检索所有 未 完成的 Whole(即 total_value 不同于单个值的总和).

For a full example of what I'm trying to achieve, see this working SQL Fiddle. I broke it down into two questions (other one here). In this case, I have a model Whole that represents a value that must be reached. Several Parts contribute to it with a (calculated) value of their own. I want to retrieve all the Wholes which has not been completed yet (i.e. the total_value is different from the sum of individual values).

select w.*
  from whole w
  where w.total_value != (
    select sum(value expression)
      from part p
      where p.whole_id = w.id
      group by p.whole_id
  );

我不知道如何(或者是否可能)使用 Django ORM 来做到这一点.我见过 很多 例子 个使用 __in 的子查询(并且可以通过 print qs.query 确认结果确实作为单个查询运行)，但前提是两个查询都是独立的彼此的.在这里，子查询受父查询 (w.id) 中的字段约束.我想过使用 F()、Q() 甚至 extra，但不知道该怎么做...

I don't know how (or if it's even possible) to do this using Django ORM. I've seen many examples of subqueries using __in (and could confirm by print qs.query that the result is indeed ran as a single query), but only when both queries are independent of each other. Here, the subquery is constrained by a field in the parent query (w.id). I thought of using F(), Q(), or even extra, but can't quite figure out what to do...

这是一个 SSCCE，如果有人想尝试一下:下载 或 浏览.它与上面链接的 SQL fiddle 具有相同的模型和数据.

Here's a SSCCE, in case anyone want to experiment with it: Download or Browse. It has the same models and data as the SQL fiddle linked above.

更新: 对于我的特殊情况，我发现不需要进行子查询，我可以使用 group by 和 having(如 this SQL Fiddle 所示):

Update: for my particular case, I found out there's no need to do a subquery, I can just use group by and having (as this SQL Fiddle shows):

q = Q(part__isnull=True) | ~Q(partial=F('total_value'))
qs = Whole.objects.annotate(partial=Sum(...)).filter(q).distinct()

# And if total_value can be zero:
qs = qs.exclude(part__isnull=True, total_value=0)

子查询的一般情况仍然没有解决(没有使用一些原始 SQL，如 我在下面的回答所示).

The general case for subqueries is still unsolved though (short of using some raw SQL, as my answer below shows).

推荐答案

我用最少原始 SQL 设计的解决方案使用 extra 和 where:

The solution I devised with the least raw SQL uses extra and where:

• 首先创建内部查询；使用 extra 指定一个自定义 where 组件，将受限字段与外部查询中的字段进行比较，因为它会出现在那里(可能需要硬编码表名/别名):

• First create the inner query; use extra to specify a custom where component, comparing the restricted field to the one in the outer query, as it will appear there (might need to hardcode the table name/alias):

qs1 = Part.objects.extra(where=['whole_id = "applabel_whole"."id"'])...

然后对其进行剩余的操作(本例中使用values和annotate对单个字段进行分组、聚合和返回).

Then make the remaining operations on it (in this case, using values and annotate for grouping, aggregation and return of a single field).

然后在外层查询中包含内层查询(使用.query)生成的SQL，同样使用extra和where:

Then include the generated SQL of the inner query (using .query) in the outer query, also using extra and where:

qs = Whole.objects.extra(where=['total_value != ({})'.format(qs1.query)])

extra 调用中的代码片段可能不可移植(例如:一些后端使用 !=，其他使用 <>，引用表名的正确方法可能会有所不同，等等)，但内部查询的其余部分应该是(因为它是由 ORM 生成的).

The code fragment in the extra calls might not be portable (ex.: some backends use !=, others use <>, the correct way of quoting table names might vary, etc), but the rest of the inner query shall be (since it was generated by the ORM).

生成的查询对应于我要查找的内容(聚合部分除外，另一个问题).为便于阅读而格式化的 SQL:

The resulting query corresponds to what I'm looking for (except for the aggregation part, which is covered in the other question). SQL formatted for readability:

>>> qs1 = Part.objects.extra(
        where=['whole_id = "aggregation_subquery_whole"."id"']
    ).values('whole_id').annotate(sum=Sum('before__value')).values('sum')

>>> qs = Whole.objects.extra(where=['total_value != ({})'.format(qs1.query)])

>>> print qs.query

SELECT "aggregation_subquery_whole"."id",
       "aggregation_subquery_whole"."total_value" 
FROM "aggregation_subquery_whole"
WHERE total_value != (
    SELECT SUM("aggregation_subquery_sequence"."value") AS "sum"
    FROM "aggregation_subquery_part"
        LEFT OUTER JOIN "aggregation_subquery_sequence" ON
           ("aggregation_subquery_part"."before_id" =
            "aggregation_subquery_sequence"."id") 
    WHERE whole_id = "aggregation_subquery_whole"."id"
    GROUP BY "aggregation_subquery_part"."whole_id"
)

这篇关于在 Django 子查询中，我可以引用“父"吗?询问?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！