How to use Django annotate?

Question

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

Can you share the relevant parts of the Contract and Order model? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Feb 16, 2020 at 14:42

willeM_ Van Onsem · Accepted Answer · 2020-02-16 14:43:52Z

2

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

answered Feb 16, 2020 at 14:43

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

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

Collectives™ on Stack Overflow

How to use Django annotate?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related