跨越多个表的Django查询中的表达式聚合 [英] Aggregation of an expression in Django query spanning multiple tables

问题描述

能否在Django ORM查询中从相关表中计算字段的表达式(例如:减法)的合计(例如:总和)?

Is it possible to compute an aggregation (ex.: sum) of an expression (ex.: subtraction) of fields from a related table in a Django ORM query?

有关我要实现的目标的完整示例，请参见此工作 SQL Fiddle .我将其分解为两个问题(其他问题在这里).在这种情况下，我有一个代表数字序列的模型Sequence和一个代表该序列中链接"的模型Part:

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 Sequence that represents a sequence of numbers, and a model Part that represent a "link" in this sequence:

Sequence   Sequence   Sequence   Sequence   ...
0          5          20         15         ...
|-- Part --|-- Part --|-- Part --|-- ...

每个Part因此表示一个 delta ，它是序列中两个值之间的差.我有一组(非连续的)部分，想要计算这些增量的总和.像这样:

Each Part thus represents a delta, which is the difference between the two values in the sequence. I have a set of (non-contiguous) parts and want to calculate the sum of those deltas. Something like this:

sum([p.after.value - p.before.value for p in Part.objects.filter(...)])

但是在单个查询中(原因是我想将此作为更复杂查询的子查询).在SQL中很容易做到:

But in a single query (the reason is that I want to use this as a subquery for a more complex one). It's easy to do it in SQL:

select sum(a.value - b.value)
  from part p
    join sequence a on p.after_id = a.id
    join sequence b on p.before_id = b.id
  where condition
  group by p.other_field;

我不知道如何使用Django ORM做到这一点.我可以将其设置为单个值:

I don't know how to do it using Django ORM. I can do it for a single value:

Part.objects.filter(condition).aggregate(Sum('before__value')).values()

但不适用于涉及多个值的表达式.目前尚不支持使用F() ，因此，我正在寻找一种替代方法.我还查看了这个问题，它也与聚合表达式有关，但

But not for an expression involving multiple values. Using F() is not supported yet, so I'm looking for an alternative way. I also looked at this question, which is also about expressions in aggregates, but the accepted answer (using extra) is not applicable to my case AFAIK, since the fields I'm interested in are not in the same table, but in a related one.

>>> Part.objects.filter(condition).extra(select={
...     'delta':'sum(after__value - before__value)'
... })

DatabaseError: no such column: after__value

这里是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.

推荐答案

现在，该版本1.8已实现对在aggregate 中的表达式，答案变得很简单:

Now that 1.8 implemented support for F expressions in aggregate, the answer became straightforward:

Part.objects.filter(condition).aggregate(sum=Sum(F('after__value')-F('before__value'))).values()

这篇关于跨越多个表的Django查询中的表达式聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！