Grouping data by field with the Django ORM
December 7, 2013 Comments
I just finished creating some charts for my Django app using Highcharts and had to group some data for them. I thought it would be just as simple as calling a group_by() method but turned out it works a little differently but still pretty straightforward and well documented.
Just to show an example (I’m using Django 1.6):
# glucoses/models.py class Category(models.Model): name = models.CharField(unique=True, max_length=255) class Glucose(TimeStampedModel): user = models.ForeignKey(User) value = models.PositiveIntegerField() # in mg/dL category = models.ForeignKey('Category') ...
If I want to group the data by category, take the average of the values , and count the number of records for that group, I would write something like this:
from django.db.models import Avg, Count from glucoses.models import Glucose data = Glucose.objects.values('category__name')\ .annotate(num_values=Count('value'), average=Avg('value'))\ .order_by('-average')
The output is a ValuesQuerySet object that looks something like this:
[ {'category__name': u'Bedtime', 'average': 154.62, 'num_values': 51}, {'category__name': u'Dinner', 'average': 151.2, 'num_values': 60}, {'category__name': u'Lunch', 'average': 144.73, 'num_values': 73}, {'category__name': u'Breakfast', 'average': 142.17, 'num_values': 57} ]
The key here for grouping by field is the values() clause. You can then use annotate() to do calculations for the grouped values.