python – How to query as GROUP BY in django?

The Question :

366 people think this question is useful

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.

The Question Comments :

The Answer 1

534 people think this answer is useful

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

The Answer 2

59 people think this answer is useful

An easy solution, but not the proper way is to use raw SQL:

results = Members.objects.raw('SELECT * FROM myapp_members GROUP BY designation')

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.

The Answer 3

43 people think this answer is useful

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/2.1/ref/templates/builtins/#regroup

edit: note the regroup tag does not work as you would expect it to if your list of dictionaries is not key-sorted. It works iteratively. So sort your list (or query set) by the key of the grouper before passing it to the regroup tag.

The Answer 4

6 people think this answer is useful

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

The Answer 5

6 people think this answer is useful

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.

The Answer 6

6 people think this answer is useful

The following module 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

The Answer 7

1 people think this answer is useful

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.

The Answer 8

-2 people think this answer is useful

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

The Answer 9

-4 people think this answer is useful
from django.db.models import Sum
Members.objects.annotate(total=Sum(designation))

first you need to import Sum then ..

Add a Comment