Sumproduct using Django's aggregation

Question

Question

• Is it possible using Django's aggregation capabilities to calculate a sumproduct?

Background

I am modeling an invoice, which can contain multiple items. The many-to-many relationship between the Invoice and Item models is handled through the InvoiceItem intermediary table.

The total amount of the invoice—amount_invoiced—is calculated by summing the product of unit_price and quantity for each item on a given invoice. Below is the code that I'm currently using to accomplish this, but I was wondering if there is a better way to handle this using Django's aggregation capabilities.

Current Code

class Item(models.Model):
    item_num = models.SlugField(unique=True)
    description = models.CharField(blank=True, max_length=100)


class InvoiceItem(models.Model):
    item = models.ForeignKey(Item)
    invoice = models.ForeignKey('Invoice')
    unit_price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.DecimalField(max_digits=10, decimal_places=4)


class Invoice(models.Model):
    invoice_num = models.SlugField(max_length=25)
    invoice_items = models.ManyToManyField(Item,through='InvoiceItem')

    def _get_amount_invoiced(self):
        invoice_items = self.invoiceitem_set.all()
        amount_invoiced = 0
        for invoice_item in invoice_items:
            amount_invoiced += (invoice_item.unit_price *
                invoice_item.quantity)
        return amount_invoiced

    amount_invoiced = property(_get_amount_invoiced)        

Answer 1

Yes, it is possible since Django 1.1 where aggregate functions were introduced. Here's a solution for your models:

 def _get_amount_invoiced(self):
     self.invoiceitem_set.extra(select=("item_total": "quantity * unit_price")
                                ).aggregate(total=Sum("item_total")["total"]

It is, however, highly recommended to store item_total in a database, because it may be subject to discounts, taxes and other changes that make calculating it evety time impractical or even impossible.