How to query as GROUP BY in django

Question

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.

Answer 1

Hii,

Django does not support free group by queries. I learned it in the very bad way. ORM is not designed to support stuff like what you want to do, without using custom SQL. You are limited to:

• RAW sql (i.e. MyModel.objects.raw())
• cr.execute sentences (and a hand-made parsing of the result).
• .annotate() (the group by sentences are performed in the child model for .annotate(), in examples like aggregating lines_count=Count('lines'))).

Over a queryset qs you can call qs.query.group_by = ['field1', 'field2', ...] but it is risky if you don't know what query are you editing and have no guarantee that it will work and not break internals of the QuerySet object. 

Besides, it is an internal (undocumented) API you should not access directly without risking the code not being anymore compatible with future Django versions.

Hope this works!!

Thank You!!