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?
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.
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() .
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.
¶ Django allows using SQL subqueries.
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:
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()
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 ofannotate()
,values()
,order_by()
and thedjango.db.models
'sCount
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 ... | ...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With