如何将两个注释的查询集合合并成一个结果 [英] how to merge two annotated querysets into one result

问题描述

型号：

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的已知限制： https://code.djangoproject.com/ticket/10060 。

Your problem is a known limitation of Django's ORM: https://code.djangoproject.com/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

这篇关于如何将两个注释的查询集合合并成一个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！