Django - Annotating multiple Sum() object gives wrong result

user_11802316 0
Viewed 753

models.py looks like this

class Channel(Model):
    name = CharField()

class Contract(Model):
    channel = ForeignKey(Channel, related_name='contracts')
    fee = IntegerField()

class ContractPayment(Model):
    contract = ForeignKey(Contract, related_name='payments')
    value = IntegerField()

When I query a model:

Channel.objects.annotate(pay=Sum('contracts__fee'))

It returns: 75000. And Its correct but when I query like this:

Channel.objects.annotate(pay=Sum('contracts__fee'))
               .annotate(paid=Sum('contracts__payments__value'))


And it returns: pay: 96000, paid: 33000. As you can see the pay is changed. What is going on here? I read the ticket #10060 but no luck.

django-queryset django-models django
3 Answers

Use two queries to get the desired result:

Channel.objects.annotate(pay=Sum('contracts__fee'))

and 

Channel.objects.annotate(paid=Sum('contracts__payments__value'))
user_2900360

I think you have to use distinct=True in annotate() as below...

Channel.objects.annotate(pay=Sum('contracts__fee', distinct=True)).annotate(paid=Sum('contracts__payments__value', distinct=True))
user_109001700

If you're not using Django3, I think this should work:

from django.db.models import Subquery, OuterRef, Sum
contracts = Contract.objects.filter(channel__id=OuterRef("id")).values("fee")
tot = contracts.values("channel_id").annotate(total=Sum("fee")).values("total")
Channel.objects.annotate(pay=Subquery(tot), paid=Sum('contracts__payments__value')).values()
user_56599660
Related