How to query as GROUP BY in Django?
django, django-models, group-by, python
Solution
If you mean to do aggregation you can use the aggregation features of the ORM:
from django.db.models import Count
result = (Members.objects
.values('designation')
.annotate(dcount=Count('designation'))
.order_by()
)
This results in a query similar to
SELECT designation, COUNT(designation) AS dcount
FROM members GROUP BY designation
and the output would be of the form
[{'designation': 'Salesman', 'dcount': 2},
{'designation': 'Manager', 'dcount': 2}]
If you don't include the `order_by()`, you may get incorrect results if the default sorting is not what you expect.
If you want to include multiple fields in the results, just add them as arguments to `values`, for example:
.values('designation', 'first_name', 'last_name')
References:
- Django documentation: `values()`, `annotate()`, and `Count`
- Django documentation: Aggregation, and in particular the section entitled Interaction with default ordering or `order_by()`
Problem
I query a model: ``` Members.objects.all() ``` And it returns: ``` Eric, Salesman, X-Shop Freddie, Manager, X2-Shop Teddy, Salesman, X2-Shop Sean, Manager, X2-Shop ``` What I want is to know the best Django way to fire a `group_by` query to my database, like: ``` Members.objects.all().group_by('designation') ``` Which doesn't work, of course. I know we can do some tricks on `django/db/models/query.py`, but I am just curious to know how to do it without patching.