Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django GROUP BY field value

Tags:

python

django

Surprising that I could not find a way to make a group by query.

I have a query set qs and I am trying to group by some_prop.val, considering qs is excluding entries where some_prop is None.

Let's say the values are [1, 2, 3], then I would be after a result like this:

{1: entries1, 2: entries2, 3: entries3}

Does the Django ORM provide any feature to group results like this?

like image 850
dabadaba Avatar asked Jul 18 '17 09:07

dabadaba


People also ask

What is Groupby in Django?

A GROUP BY in Django is used with the AGGREGATE functions or the built-ins. The GROUP BY statement and aggregate functions like COUNT, MAX, MIN, SUM, AVG are used in a combo to group the result – set by one or more columns.

What is difference between annotate and aggregate in Django?

Unlike aggregate() , annotate() is not a terminal clause. The output of the annotate() clause is a QuerySet ; this QuerySet can be modified using any other QuerySet operation, including filter() , order_by() , or even additional calls to annotate() .

What is OuterRef Django?

to Django users. According to the documentation on models. OuterRef: It acts like an F expression except that the check to see if it refers to a valid field isn't made until the outer queryset is resolved.

Does Django ORM support subquery?

¶ Django allows using SQL subqueries.


1 Answers

There is not a specific Django ORM way (as far as I know) but you can do the following to get a dictionary of entries grouped by values of a field:

  1. Use .values_list() with flat=True to get a list of the existent values in your database (if you don't know them beforehand). Also use .distinct() to eliminate duplicate values as we do not care for those:

    value_list = MyModel.objects.values_list(
        'interesting_field', flat=True
    ).distinct()
    
  2. Now iterate through value_list and fill your dictionary:

    group_by_value = {}
    for value in value_list:
        group_by_value[value] = MyModel.objects.filter(interesting_field=value)
    

Now group_by_value dictionary contains as keys the distinct values in your interesting_field and as values the queryset objects, each containing the entries of MyModel with interesting_field=a value from value_list.




Leaving this here for comment legacy reasons.

I have made a Q&A style example in, which simulates a COUNT ... GROUP BY SQL query.

Essentially you need to utilize the .order_by for grouping and the .annotate() to count on the model's .values().

Here is the above-mentioned example:

We can perform a COUNT ... GROUP BY SQL equivalent query on Django ORM, with the use of annotate(), values(), order_by() and the django.db.models's Count methods:

Let our model be:

class Books(models.Model):
    title  = models.CharField()
    author = models.CharField()

Lets assume that we want to count how many book objects per distinct author exist in our Book table:

result = Books.objects.values('author')
                      .order_by('author')
                      .annotate(count=Count('author'))

Now result contains a queryset with two columns: author and count:

  author    | count
------------|-------  
 OneAuthor  |   5 
OtherAuthor |   2    
   ...      |  ...
like image 101
John Moutafis Avatar answered Sep 20 '22 14:09

John Moutafis