How to query as GROUP BY in django?

Question

I query a model,

Members.objects.all()

and it returns say

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 db, as 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

If I'm not mistaking you can use, whatever-query-set.group_by=['field']

Answer 2

The document says that you can use values to group the queryset .

class Travel(models.Model):
    interest = models.ForeignKey(Interest)
    user = models.ForeignKey(User)
    time = models.DateTimeField(auto_now_add=True)

# Find the travel and group by the interest:

>>> Travel.objects.values('interest').annotate(Count('user'))
<QuerySet [{'interest': 5, 'user__count': 2}, {'interest': 6, 'user__count': 1}]>
# the interest(id=5) had been visited for 2 times, 
# and the interest(id=6) had only been visited for 1 time.

>>> Travel.objects.values('interest').annotate(Count('user', distinct=True)) 
<QuerySet [{'interest': 5, 'user__count': 1}, {'interest': 6, 'user__count': 1}]>
# the interest(id=5) had been visited by only one person (but this person had 
#  visited the interest for 2 times

You can find all the books and group them by name using this code:

Book.objects.values('name').annotate(Count('id')).order_by() # ensure you add the order_by()

You can watch some cheet sheet here.

Answer 3

If you mean to do aggregation you can use the aggregation features of the ORM:

from django.db.models import Count
Members.objects.values('designation').annotate(dcount=Count('designation'))

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}]