Django: show the sum of related Items in a template

user_13520223 0
Viewed 367

I want to get the sum of all values from items related to a certain object. In concrete, I have invoices with one or many items. Each item has a price and I want to calculate the sum of the prices for all the items of one invoice.

These are the essential parts of my models.py:

from django.db import models
from django.db.models import Sum

class Invoice(models.Model):

    @property
    def price(self): 
        return self.item_set.aggregate(Sum('price'))

class Item(models.Model):
    invoice = models.ForeignKey(Invoice, on_delete=models.CASCADE)
    price = models.DecimalField(max_digits=8, decimal_places=2)

The view.py is simple:

class InvoiceDetailView(DetailView):
    model = Invoice

In my template invoice_detail.html, {{ invoice.price }} delivers something like

{'price__sum': Decimal('282.400000000000')}

So I added get('price__sum') to the price property of the Invoice Model like that:

   return self.item_set.aggregate(Sum('price')).get('price__sum')

The result actually is fine now, i.e. the template shows something like 282.400000000000 (which I still could style a bit), but I wonder, if that's the way to do something like that or if there is a better way?

django-views django-models django
1 Answers

It is better to work with .annotate(…) [Django-doc] since then you aggregate in the same query. This is actually a lot better in a ListView: if you would use a property in a ListView, this will result in an N+1 problem where you make one query to fetch the Invoices, and n extra queries to obtain the price for each Invoice.

You thus can thus annotate in the view:

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

class InvoiceDetailView(DetailView):
    model = Invoice
    queryset = Invoice.objects.annotate(
        price_=Coalesce(Sum('item__price'), Value(0))
    )

The Coalesce [Django-doc] is necessary to prevent returning NULL/None in case there are no related items.

then in the view you can again use {{ invoice.price_ }}. We can not use price, since there is already a property for this.

You can however let annotate and the property work together with:

class Invoice(models.Model):
    # …

    @property
    def price(self):
        if hasattr(self, 'price_'):
            return self.price_
        return self.item_set.aggregate(
            price=Coalesce(Sum('price'), Value(0))
        )['price']

Here we thus first look if there is a .price_ attribute, and if there is, we will not make an extra query.

user_675790
Related