在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小提琴。我将其分解为两个问题（其他问题在这里）。在这种情况下，我有一个模型 Whole 表示必须达到的值。几个 Part 都以其自己的（计算得出）值来贡献它。我想检索尚未完成的所有整个（即 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（）甚至什至额外，但不能完全确定该怎么做...

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提琴相同的模型和数据。

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 和拥有（如此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 和其中：

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"'])...

然后对其进行其余操作（在这种情况下，使用值和 annotate 进行分组，汇总和返回）

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

然后包括内部查询的生成SQL（使用 .query ）在外部查询中，还使用 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屋！