Get aggregate in a template

Question

I have this Django model:

class Location(models.Model):
    name = models.CharField(primary_key=True, max_length=100)
    customer = models.OneToOneField(Customer, default=None)

class Customer(models.Model):
    name = models.CharField(primary_key=True, max_length=100)

class Order(models.Model):
    amount = models.PositiveIntegerField(default=0)
    customer = models.ForeignKey(Customer, default=0)

in my view I get them like this:

locations = models.Location.objects.all()

and the template lists them like this:

{% for location in locations %}
    {{ location.customer.name }}
{% endfor %}

I would like to add a sum of all amount of all Orders connected that customer, something like:

{% for location in locations %}
    {{ location.customer.name }} ordered {{ location.customer.orders.sum(amount) }} items
{% endfor %}

And according to this question, I should do that in the view, but how?

Er, by following the [aggregation docs](https://docs.djangoproject.com/en/1.10/topics/db/aggregation/)? Where exactly are you having problems? — Daniel Roseman, Nov 02 '16 at 10:21
@DanielRoseman I updated the question with what I tried, trouble is in the fact that I have another model (I posted the incorrect model before). — Bart Friederichs, Nov 02 '16 at 13:06

score 2 · Accepted Answer · answered Nov 02 '16 at 12:31

2

You should use .annotate (look in docs):

from django.db.models import Count

customers = models.Customer.objects.annotate(orders_count=Count('order'))

Then in templates you can use it like this:

{% for customer in customers %}
    {{ customer.name }} ordered {{ customer.orders_count }} items
{% endfor %}

answered Nov 02 '16 at 12:31

devxplorer

678
1
4
10

I got it working. Is there a way to filter some `order`s out? – Bart Friederichs Nov 02 '16 at 13:11
@BartFriederichs give an example of what you want please – devxplorer Nov 02 '16 at 13:16
I only want to count the orders that have a certain state. – Bart Friederichs Nov 02 '16 at 13:17
@BartFriederichs probably you can do this with [When object](https://docs.djangoproject.com/en/1.10/ref/models/conditional-expressions/#when) – devxplorer Nov 02 '16 at 13:19
@BartFriederichs look at [this question](http://stackoverflow.com/questions/33775011/how-to-annotate-count-with-a-condition-in-a-django-queryset) – devxplorer Nov 02 '16 at 13:23
Looks promising, but I keep getting `__init__() takes either a Q object or lookups as keyword arguments`. – Bart Friederichs Nov 02 '16 at 13:31

score 0 · Answer 2 · answered Nov 02 '16 at 13:20

After some folling around I found this works:

locations = models.Location.objects.annotate(num_order=Count('customer__order'))

and then use this in the template:

{% for location in locations %}
    {{ location.customer.name }} ordered {{ location.num_order }} items
{% endfor %}

Get aggregate in a template

2 Answers2