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?

+3


source to share


3 answers


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"

      

+2


source


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.

+1


source


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]

      

+1


source







All Articles