Calculate the difference of 2 aggregated Sum in one query

Question

I have a model as follows:

class Loan(models.Model):
    lender = models.ForeignKey(User, related_name='lender')
    borrower = models.ForeignKey(User, related_name='borrower')
    money = models.IntegerField()

What I want to do is to calculate gross total for a user:

gross_total = Loans.object.filter(lender=user).annonate(Sum('money')) 
                - Loans.object.filter(borrower=user).annonate(Sum('money'))

My current total calculation is using two queries. I would like a single query for this.

Thanks.

score 4 · Accepted Answer · answered Jul 20 '16 at 16:07

4

This can be done with conditional expressions

from django.db.models import Case, Value, When, IntegerField

gross_total = Loans.object.aggregate(
    gross=Sum(
        Case(
            When(lender=user, then=F('money')),
            default=0, output_field=IntegerField)
        )
    ) - Sum(
        Case(
            When(borrower=user, then=F('money')),
            default=0, output_field=IntegerField)
        )
    )
)

answered Jul 20 '16 at 16:07

Sardorbek Imomaliev

14,861
2
51
63

That's exactly what i need, didn't even know that we can do that in django. Thanks!! :) – s0nskar Jul 20 '16 at 16:25
I needed to call `IntegerField` like `output_field=IntegerField()` – Michel Rugenbrink May 01 '19 at 09:56

score 0 · Answer 2 · edited May 23 '17 at 12:32

0

Firstly, as you are using two Foreign Keys from the same User model, you need to give them a 'related_name' like this:

class Loan(models.Model):
    lender = models.ForeignKey(User, related_name='lender')
    borrower = models.ForeignKey(User, related_name='borrower')
    money = models.IntegerField()

Now coming to your question, what you need is something given in this post -> How to group by AND aggregate with Django

So the desired query for your question will most probably be:

gross_total = Loans.objects.filter(lender=user).values('user').annotate(score = Sum('money')) - Loans.objects.filter(borrower=user).values('user').annotate(score = Sum('money'))

edited May 23 '17 at 12:32

Community

1
1

answered Jul 20 '16 at 12:20

Ankush Raghuvanshi

1,392
11
17

that's not what is want, I edited the question. btw thanks for that `value('user')` thing didn't knew about it – s0nskar Jul 20 '16 at 12:36
1

Not a problem. If you want to a single query, then I suppose you need an F() expression. See this -> https://docs.djangoproject.com/en/1.9/ref/models/expressions/ I'm trying to come up with the required answer to your updated question. If i will, then i'll add another answer. – Ankush Raghuvanshi Jul 20 '16 at 15:55

Calculate the difference of 2 aggregated Sum in one query

2 Answers2