I’m doing some typical display list of orders for certain customers together with the total price of each order. In the past what I would did is just some sql joining orders table and order_items and getting the sum of each price column in the order_items table. But since now I’m using Django, I thought there must be some way to do this through Django API without getting my hands dirty with the plain sql anymore.

Simple Google search on ‘django aggregation’ quickly took me to a blog post with a link to the Django documentation page. Turn out aggregation support first landed in Django 1.1. So how to use it ? This is my models:-

class Item(models.Model):
    id = models.IntegerField(primary_key=True)
    description = models.TextField()
    price = models.FloatField()
    name = models.CharField(unique=True, max_length=-1)
    komisen = models.FloatField()
    stock_threshold = models.IntegerField()
    category = models.CharField(max_length=-1)
    komisen_ahli = models.FloatField()
   status = models.IntegerField()

class Order(models.Model):
    id = models.IntegerField(primary_key=True)
    transaction_date = models.DateField()
    customer = models.ForeignKey(Customer)
    status = models.IntegerField()
    payment_due_date = models.DateField()
    category = models.CharField(max_length=-1)
    uid = models.ForeignKey(User, db_column='uid')
    items = models.ManyToManyField(Item, through='OrderItem')

What I want is some output like:-

ID |      Date       |   Total
--------------------------------------
 8 |  12-01-2009 |  89.00
 9 |  12-01-2009 |  100.00

And here’s the Django code to get it:-

from django.db.models import Sum

orders = Order.object.filter(customer=1301).annotate(total_price=Sum('items__price'))

Yup, it’s only one line. And here’s to see the actual query executed by Django, just what I would did manually:-

from django.db import connection
connection.queries.pop()

{'time': '0.005', 'sql': 'SELECT "orders"."id", "orders"."transaction_date", "orders"."customer_id", "orders"."status", "orders"."payment_due_date", "orders"."category", "orders"."uid", SUM("items"."price") AS "total_price" FROM "orders" LEFT OUTER JOIN "order_items" ON ("orders"."id" = "order_items"."order_id") LEFT OUTER JOIN "items" ON ("order_items"."item_id" = "items"."id") WHERE "orders"."customer_id" = 1301  GROUP BY "orders"."id", "orders"."transaction_date", "orders"."customer_id", "orders"."status", "orders"."payment_due_date", "orders"."category", "orders"."uid" LIMIT 1 OFFSET 2'}

p/s: Markdown screw up the formatting :(