Django: aggregating submodule fields
I currently have the following models where there is a class Product
that has a lot Ratings
. Each Rating
has a date_created
DateTime field and a field stars
that is an integer from 1 to 10. Is there a way that I can add the total number of stars assigned to all products on a specific day, for all days?
For example, on December 21, 543 stars were awarded to all Products in general (that is, 200 for item A, 10 for item B, 233 at item C). The next day might be 0 stars because there were no ratings for any Products.
I can imagine how to get a list of dates first and then filter each date and aggregate each one, but that seems very intensive. Is there an easier way?
source to share
You can do it all in one request 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
It should output something like:
[{'date_created': '1-21-2013', 'total': 150}, ... ]
The SQL for it looks like this (the WHERE clause is omitted):
SELECT "myapp_ratings"."date_created", SUM("myapp_ratings"."stars") AS "total" FROM "myapp_ratings" GROUP BY "myapp_ratings"."date_created"
source to share
You want to use Django's aggregation functions; in particular 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 script doesn't need to use any submodels at all. Since the field date_created
and field stars
are both members of the model Rating
, you can simply query it directly.
source to share
You can always just execute 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]
source to share