[Django]-Django: Aggregating Across Submodels Fields

by

2👍

You should be able to do it all in one query, using values:

from datetime import date, timedelta
from django.db.models import Sum

end_date = date.now()
start_date = end_date - timedelta(days=7)

qs = Rating.objects.filter(date_created__gt=start_date, date_created__lt=end_date)
qs = qs.values('date_created').annotate(total=Sum('stars'))

print qs

Should output something like:

[{'date_created': '1-21-2013', 'total': 150}, ... ]

The SQL for it looks like this (WHERE clause omitted):

SELECT "myapp_ratings"."date_created", SUM("myapp_ratings"."stars") AS "total" FROM "myapp_ratings" GROUP BY "myapp_ratings"."date_created"
👤Aaron Lisman

1👍

You’ll want to use Django’s aggregation functions; specifically, Sum.

>>> from django.db.models import Sum
>>>
>>> date = '2012-12-21'
>>> Rating.objects.filter(date_created=date).aggregate(Sum('stars'))
{'stars__sum': 543}

As a side note, your scenario actually doesn’t need to use any submodels at all. Since the date_created field and the stars field are both members of the Rating model, you can just do a query over it directly.

👤voithos

1👍

You could always just perform some raw SQL:

from django.db import connection, transaction
cursor = connection.cursor()
cursor.execute('SELECT date_created, SUM(stars) FROM yourapp_rating GROUP BY date_created')
result = cursor.fetchall()  # looks like [('date1', 'sum1'), ('date2', 'sum2'), etc]
👤mVChr

Leave a comment