Django GROUP BY field value
There is not a specific Django ORM way (as far as I know) but you can do the following to get a dictionary of entries grouped by values of a field:
Use
.values_list()
withflat=True
to get a list of the existent values in your database (if you don't know them beforehand). Also use.distinct()
to elimintae duplicate values as we do not care for those:value_list = MyModel.objects.values_list( 'interesting_field', flat=True).distinct()
Now iterate through
value_list
and fill your dictionary:group_by_value = {}for value in value_list: group_by_value[value] = MyModel.objects.filter(interesting_field=value)
Now group_by_value
dictionary contains as keys the distinct values in your interesting_field
and as values the queryset objects, each containing the entries of MyModel
with interesting_field=a value from value_list
.
Leaving this here for comment legacy reasons.
I have made a Q&A style example in, which simulates a COUNT ... GROUP BY
SQL query.
Essentially you need to utilize the .order_by
for grouping and the .annotate()
to count on the model's .values()
.
Here is the above-mentioned example:
We can perform a
COUNT ... GROUP BY
SQL equivalent query on Django ORM, with the use ofannotate()
,values()
,order_by()
and thedjango.db.models
'sCount
methods:Let our model be:
class Books(models.Model): title = models.CharField() author = models.CharField()
Lets assume that we want to count how many book objects per distinct author exist in our Book table:
result = Books.objects.values('author') .order_by('author') .annotate(count=Count('author'))
Now result contains a queryset with two columns: author and count:
author | count------------|------- OneAuthor | 5 OtherAuthor | 2 ... | ...