Django ORM annotate Sum calculation wrong and its multiplying by number of entry, it's wired

Question

I am going through a wired situation in Django ORM. It is returning a wrong calculation of sum and unexpectedly it is multiplied by the number of entries which I don't want, it's behave completely wired.

These are my models

class Person(models.Model):
    name = models.CharField(max_length=100)



class Purchase(models.Model):
    person = models.ForeignKey(
        Person,
        on_delete=models.CASCADE,
        related_name='person_purchase'
    )

    amount = models.DecimalField(decimal_places=2, max_digits=5)



class Consumption(models.Model):
    person = models.ForeignKey(
        Person,
        on_delete=models.CASCADE,
        related_name='person_consumption'
    )

    amount = models.DecimalField(decimal_places=2, max_digits=5)

This is my query:

person_wise_payable = Person.objects.annotate(
            difference=ExpressionWrapper(
                Coalesce(Sum('person_purchase__amount'), Value(0)) - Coalesce(Sum('person_consumption__amount'), Value(0)),
                output_field=FloatField()
            ),
        )

    for i in person_wise_payable:
        print(i.difference, i.name)

I am trying to find out the difference between person wise Purchase and Consumption.

For example, i have 3 person, foo, doe, jhon

and these are their entry

Purchase models entries:
foo = 5
doe = 2
doe = 3
doe = 3


Consumption models entries:

foo = 1
foo = 2
foo = 2
doe = 1
doe = 1
jhon = 2

So you see above,

foo total purchase is 5
doe total purchase is 8
jhon total purchase is 0 (coz, no entry of him)

and

foo total consumption is 5
doe total consumption is 2
jhon total consumption is 2

So expected output/difference if we subtract the consumption from purchase

foo : 5 - 5 =   0
doe: 8 - 2 =   6
jhon: 0 - 2 =   -2

I hope you got the point what i want to do and what is expected output.

foo 0, doe 6 and jhon -2

But the problem is, My current query not returning the output like this above, it is returning very very much wired, see the wired result below.

-2.0 jhon
10.0 doe
10.0 foo

Can anyone help me how can I get it done correctly? I am struggling with for last few days, couldn't achieve this yet

Answer 1

If you make multiple JOINs, teh they act as a multiplier, since you make a query

SELECT SUM(purchase.amount) - SUM(consumption.amount)
FROM person
LEFT OUTER JOIN purchase
LEFT OUTER JOIN consumption

so the same purchase.amount is repeated that many times as there are related consumptions, and the same consumption.amount is repeated that many times as there are related purchases.

You can solve this with a subquery, for example with:

person_wise_payable = Person.objects.annotate(
    difference=Coalesce(Subquery(
        Purchase.objects.filter(
            person=OuterRef('pk')
        ).values('person').annotate(
            sum=Sum('amount')
        ).values('sum')[:1]
    ), Value(0)) - Coalesce(Subquery(
        Consumption.objects.filter(
            person=OuterRef('pk')
        ).values('person').annotate(
            sum=Sum('amount')
        ).values('sum')[:1]
    ), Value(0))
)