CODEWITHSUNDEEP
djangodjango-models
Timo Junolainen
Timo Junolainen

Reputation: 304

How to use Django annotate?

I'd like to ask, how I could shrink this to one command? I understand that annotate is proper way to do this,but don't understand how.

Here is my code, which is too slow:

sum = 0
for contact in self.contacts.all():
   sum += (contact.orders.aggregate(models.Sum('total'))['total__sum'])
return sum

I'd like to get Sum for each contact, all records in total column of relevant orders.

Code above produces sum, but is sluggishly slow. It is my understand it can be done with annotate,but not sure how to use it.

Here is Contact:

class Contact(models.Model):
    company = models.ForeignKey(
        Company, related_name="contacts", on_delete=models.PROTECT)
    first_name = models.CharField(max_length=80)
    last_name = models.CharField(max_length=80, blank=True)
    email = models.EmailField()

And here is Orders:

class Order(models.Model):
    order_number = models.CharField(max_length=80)
    company = models.ForeignKey(Company, related_name="orders")
    contact = models.ForeignKey(Contact, related_name="orders")
    total = models.DecimalField(max_digits=12, decimal_places=6)
    order_date = models.DateTimeField(null=True, blank=True)

Help please

Upvotes: 1

Views: 1798

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476534

You can annotate your queryset on the Contract model with:

from django.db.models import Sum

Contract.objects.annotate(
    total_orders=Sum('orders__total')
)

The Contract objects that arise from this queryset will have an extra attribute .total_orders that contains the sum of the total field of the related Order objects.

This will thus create a query that looks like:

SELECT contract.*, SUM(order.total)
FROM contract
LEFT OUTER JOIN order ON order.contract_id = contract.id
GROUP BY contract.id

Upvotes: 2

Related Questions