如何将两个注释的查询集合合并成一个结果

本文介绍了如何将两个注释的查询集合合并成一个结果的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

型号：

class Foo(models.model): name = models.CharField(max_length = 50, blank = True, unique = True) class Bar1(models.Model): foo = models.ForeignKey('Foo') value = models.DecimalField(max_digits=10,decimal_places=2) class Bar2(models.Model): foo = models.ForeignKey('Foo') value = models.DecimalField(max_digits=10,decimal_places=2)

Clasess Bar1和Bar2是无关的，所以我不能像一个课什么会解决问题。但是这只是将问题显示为尽可能纯净的示例。

Clasess Bar1 and Bar2 are unrelated, so I can't do it as one class what would solve the problem. But this is only example to show the problem as pure as possible.

first = Foo.objects.all().annotate(Sum("bar1__value")) second = Foo.objects.all().annotate(Sum("bar2__value"))

每个这个查询包含正确的值。

each of this querysets contains correct values.

我无法将其合并到：

both = Foo.objects.all().annotate(Sum("bar1__value")).annotate(Sum("bar2__value"))

因为总和值乘法 - 这是不幸的预期行为 - 因为JOINS

Because the sum value multiplicates - this is unfortunately expected behaviour - because of JOINS

现在的问题 - 如何合并/加入第一和第二来获得两者？

And now the problem - how to merge/join first and second to get the both?

示例：

栏1：

foo | value -------------- A | 10 B | 20 B | 20

酒吧2：

foo | value -------------- A | -0.10 A | -0.10 B | -0.25

两者（值取决于进入bar1和bar2的顺序）

both (value differs depends on order of entering bar1 and bar2)

foo | bar1__value__sum | bar2__value__sum --------------------------------- A | 20 | -0.20 B | 40 | -0.50

预期结果：

foo | bar1__value__sum | bar2__value__sum --------------------------------- A | 10 | -0.20 B | 40 | -0.25

我不能使用itertools.chains，因为结果是：

I couldn't use itertools.chains because the result is:

foo | bar1__value__sum | bar2__value__sum --------------------------------- A | null | -0.20 B | null | -0.25 A | 10 | null B | 40 | null

推荐答案

您的问题是Django的ORM的已知限制： code.djangoproject/ticket/10060 。

Your problem is a known limitation of Django's ORM: code.djangoproject/ticket/10060.

如果你可以做两个查询，这里有一个选项：

If you're ok with doing two queries, here's one option:

result = Foo.objects.annotate(b1_sum=Sum("bar1__value")) bar2_sums = Foo.objects.annotate(b2_sum=Sum("bar2__value")).in_bulk() for foo in result: foo.b2_sum = bar2_sums.get(foo.pk).b2_sum