Tutorial :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.


Solution:1

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


Solution:2

An easy solution, but not in a proper way is to use RAW-SQL:

http://docs.djangoproject.com/en/dev/topics/db/sql/#topics-db-sql

Another solution is to use the group_by property:

query = Members.objects.all().query  query.group_by = ['designation']  results = QuerySet(query=query, model=Members)  

You can now iterate over the results variable to retrieve your results. Note that group_by is not documented and may be changed in future version of Django.

And... why do you want to use group_by? If you don't use aggregation, you can use order_by to achieve an alike result.


Solution:3

You need to do custom SQL as exemplified in this snippet:

Custom SQL via subquery

Or in a custom manager as shown in the online Django docs:

Adding extra Manager methods


Solution:4

You can also use the regroup template tag to group by attributes. From the docs:

cities = [      {'name': 'Mumbai', 'population': '19,000,000', 'country': 'India'},      {'name': 'Calcutta', 'population': '15,000,000', 'country': 'India'},      {'name': 'New York', 'population': '20,000,000', 'country': 'USA'},      {'name': 'Chicago', 'population': '7,000,000', 'country': 'USA'},      {'name': 'Tokyo', 'population': '33,000,000', 'country': 'Japan'},  ]    ...    {% regroup cities by country as country_list %}    <ul>      {% for country in country_list %}          <li>{{ country.grouper }}              <ul>              {% for city in country.list %}                  <li>{{ city.name }}: {{ city.population }}</li>              {% endfor %}              </ul>          </li>      {% endfor %}  </ul>  

Looks like this:

  • India
    • Mumbai: 19,000,000
    • Calcutta: 15,000,000
  • USA
    • New York: 20,000,000
    • Chicago: 7,000,000
  • Japan
    • Tokyo: 33,000,000

It also works on QuerySets I believe.

source: https://docs.djangoproject.com/en/1.11/ref/templates/builtins/#regroup


Solution:5

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.


Solution:6

There is module that allows you to group Django models and still work with a QuerySet in the result: https://github.com/kako-nawao/django-group-by

For example:

from django_group_by import GroupByMixin    class BookQuerySet(QuerySet, GroupByMixin):      pass    class Book(Model):      title = TextField(...)      author = ForeignKey(User, ...)      shop = ForeignKey(Shop, ...)      price = DecimalField(...)  

class GroupedBookListView(PaginationMixin, ListView):      template_name = 'book/books.html'      model = Book      paginate_by = 100        def get_queryset(self):          return Book.objects.group_by('title', 'author').annotate(              shop_count=Count('shop'), price_avg=Avg('price')).order_by(              'name', 'author').distinct()        def get_context_data(self, **kwargs):          return super().get_context_data(total_count=self.get_queryset().count(), **kwargs)  

'book/books.html'

<ul>  {% for book in object_list %}      <li>          <h2>{{ book.title }}</td>          <p>{{ book.author.last_name }}, {{ book.author.first_name }}</p>          <p>{{ book.shop_count }}</p>          <p>{{ book.price_avg }}</p>      </li>  {% endfor %}  </ul>  

The difference to the annotate/aggregate basic Django queries is the use of the attributes of a related field, e.g. book.author.last_name.

If you need the PKs of the instances that have been grouped together, add the following annotation:

.annotate(pks=ArrayAgg('id'))  

NOTE: ArrayAgg is a Postgres specific function, available from Django 1.9 onwards: https://docs.djangoproject.com/en/1.10/ref/contrib/postgres/aggregates/#arrayagg


Solution:7

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.


Solution:8

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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »