Django注释返回意外的Sum值

问题描述

这些是我的模特

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

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

You should write subquery for each aggregation

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