Django注释返回意外的Sum值 [英] Django annotate returning unexpected Sum value

问题描述

这些是我的模特

class Consume(models.Model):
    amount = models.FloatField(default=1)
    entry_for = models.ForeignKey(
        Person,
        on_delete=models.SET_NULL,
        related_name='consume_entry_for',
    )


class Purchase(models.Model):
    amount = models.DecimalField(
        max_digits=6,
        decimal_places=2,
        default=0.00
    )
    entry_for = models.ForeignKey(
        Person,
        on_delete=models.CASCADE,
        related_name='ledger_entry_for',
    )

这是我的查询:

person_wise_total = Person.objects.annotate(
            total_purchase=Coalesce(Sum('ledger_entry_for__amount'), Value(0)),
            total_consume=Coalesce(Sum('consume_entry_for__amount'), Value(0))
        )

例如，我在购买

amount: 2, entry_for: jhon,
amount: 3, entry_for: smith
amount: 5, entry_for: jhon,
amount: 1, entry_for: jhon,

和消费条目:

amount: 1, entry_for: jhon,
amount: 2, entry_for: smith,

根据上述数据，我的查询Sum应该为jhon返回 total_consume 为 1 ，但在total_consume中为jhon返回 3 .smith的total_consume是 2 ，这里是smith的结果，但是jhon的结果是意外的.

According to above data, my query Sum should return total_consume for jhon is 1, but it is returning 3 for jhon in total_consume and smith total_consume is 2, here smith result is expected but jhon result is unexpected.

我想，由于jhon引起的问题/错误计算在Purchase表中有 3 项，因此它与人的购买项和总消费量相当，我不确定为什么

I guess, the problem/ wrong calculation occurring because of jhon has 3 entry in the Purchase table, so it is multpliying with total entry of person's purchase and total consume amount, i am not sure why.

任何人都可以帮助我如何获得正确的计算结果吗?

Can anyone please help me how can i get the correct calculated result?

我想要，它应该返回，

jhon's total_purchase: 8, total_consume: 1,
smith's total_purchase: 3, total_consume: 2

有人可以帮助我吗?

推荐答案

当已有多个聚合记录时，Django使用联接表而不是子查询

Django uses joins tables instead of subqueries when there are multiple aggregations as documented already here

将多个聚合与anateate()结合将产生错误的结果，因为使用了连接子查询数量

Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries

对于每个变量，您都应该编写子查询聚集

You should write subquery for each aggregation

这篇关于Django注释返回意外的Sum值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！