Annotate query for calculate sum of 2 table value with Django ORM

Question

I have 2 tables

Class Billing(models.Model):
 id=models.AutoField(primary_key=True)
 .....
 #Some more fields
 ....


Class BillInfo(models.Model):
  id=models.AutoField(primary_key=True)
  billing=models.ForeignKey(Billing)
  testId=models.ForeignKey(AllTests)
  costOfTest=models.IntegerField(default=0)
  concession=models.IntegerField(default=0)

Here BillInfo is verticle table i.e one Billing has multiple BillInfo. Here I want to calculate the Sum(costOfTest - concession) for a single Billing.

Can I achieve this using single query?

Need help, Thanks in advance.

What is the ForeignKey from BillInfo to Billing?

willeM_ Van Onsem
– willeM_ Van Onsem

2018-10-22 13:27:30 +00:00
Commented Oct 22, 2018 at 13:27 — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Oct 22, 2018 at 13:27
Yes, question updated.

GAJESH PANIGRAHI
– GAJESH PANIGRAHI

2018-10-22 13:28:15 +00:00
Commented Oct 22, 2018 at 13:28 — GAJESH PANIGRAHI
– GAJESH PANIGRAHI, Commented Oct 22, 2018 at 13:28

willeM_ Van Onsem · Accepted Answer · 2018-10-22 13:39:02Z

4

You can write this as:

from django.db.models import F, Sum

Billing.objects.annotate(
    the_sum=Sum(F('billinfo__costOfTest') - F('billinfo__concession'))
)

Here every Billing object in this QuerySet will have an extra attribute .the_sum which is the sum of all costOfTests minus the concession of all related BillingInfo objects.

The SQL query that calculates this will look, approximately as:

SELECT billing.*
       SUM(billinginfo.costOfTest - billinginfo.concession) AS the_sum
FROM billing
LEFT OUTER JOIN billinginfo ON billinginfo.billing_id = billing.id
GROUP BY billing.id

So when you "materialize" the query, the query will obtain the sum for all the Billing objects in a single call.

For Billing objects without any related BillingInfo, the the_sum attribute will be None, we can avoid that by using the Coalesce [Django-doc] function:

from django.db.models import F, Sum, Value
from django.db.models.functions import Coalesce

Billing.objects.annotate(
    the_sum=Coalesce(
        Sum(F('billinfo__costOfTest') - F('billinfo__concession')),
        Value(0)
    )
)

edited Oct 22, 2018 at 13:39

answered Oct 22, 2018 at 13:29

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

GAJESH PANIGRAHI Over a year ago

BillingInfo has Billing as a ForeignKey. Getting error django.core.exceptions.FieldError: Cannot resolve keyword 'billinfo' into field.

willeM_ Van Onsem Over a year ago

@GAJESHPANIGRAHI: indeed, and as you can see in the query, we make use of the billing_id counterpart of this foreign key in the JOIN.

willeM_ Van Onsem Over a year ago

@GAJESHPANIGRAHI: the point is that if you define a ForeignKey from model A to B, Django constructs an implicit relation that refers from B to A.

GAJESH PANIGRAHI Over a year ago

But I want to access the BillInfo from the Billing model and Billing is ForeignKey of BillInfo. There is an object billinginfo_set.

willeM_ Van Onsem Over a year ago

@GAJESHPANIGRAHI: we are accessing the BillingInfo from the Billing. Notice the F('billinginfo__...'). The fact that the ForeignKey is located otherwise does not matter, since a database makes a JOIN and will normally have a proper index to do this properly. It is not because there is no explicit ForeignKey stored in the database that this is a problem to obtain the set of related BillingInfo.

Collectives™ on Stack Overflow

Annotate query for calculate sum of 2 table value with Django ORM

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related