Django: Group by?

Question

I'm looking for something like the following:

previous_invoices = Invoice.objects.filter(is_open=False)
                                   .order_by('-created')
                                   .group_by('user')

but group_by() doesn't exist...

This would find the most recently closed invoice for each user.

This aggregation API seems to let you do stuff like this for counts and sums, but I don't need a count or sum or anything, I actually want the invoice objects!

is the *selected* thing even defined when using group by? I would expect it to yield different results depending on sql implementation... — user3012759, Aug 07 '17 at 13:57
@user3012759 No. The ordering happens after grouping, which record you get back within that group is undefined. It took me awhile to really understand that. [`ONLY_FULL_GROUP_BY`](https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by) should be enabled by default IMO so that you don't make that mistake. — mpen, Aug 08 '17 at 07:26
Hey @mpen I added an more recent attempt to answer. Care to take a look? — John Moutafis, Aug 11 '17 at 09:46
@JohnMoutafis Sure I can look. I don't work with Django anymore, but that looks like it'd be helpful if I did :-) — mpen, Aug 11 '17 at 17:04

score 2 · Answer 1 · answered Mar 13 '10 at 14:20

There's this page from 2007 who hacked it to django, but that would be moot since pre 1.1 does have an undocumented group_by` anyway. But this thread from a year ago seems to have some insights. Essentially:

Django intentionally does not expose "GROUP BY" or anything like that, in the ORM. Although the fact that we're over a relational storage backend sometimes leaks through, the ORM API is fairly SQL-agnostic. Instead, we expose particular pieces of functionality that happen to be implemented using "GROUP BY" when it's turned into SQL (and could well be implemented by some entirely different set of magical fairies with a different storage system backend).

See also the mentions of using extra, and also the magical ways in which that can fail. Best of luck.

John Moutafis · Accepted Answer · 2020-05-14T14:43:11.910

Option 1:

Although a group_by() does not exist in Django, you can try and work around on retrieving the most recently closed invoice for each user by utilizing latest() method and filter for a user as well:

previous_invoices = Invoice.objects.filter(user=my_user, is_open=False)
                                   .latest('created')

For older versions of Django were latest() does not exist, the query will look like this:

previous_invoices = Invoice.objects.filter(user=my_user, is_open=False)
                                   .order_by('-created')[0]

Option 2:

UPDATE: I have since then added a Q&A style example here: How to execute a GROUP BY ... COUNT or SUM in Django ORM? that shows how to simulate a GROUP BY operation on Django ORM.

If you absolutely want to create the effects of a group_by, then you can create one manually as shown in the accepted answer here: Django GROUP BY field value.

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.

Note:

There exists this library django-group-by which claims to add a group_by() on Django you may want to check.

Django: Group by?

2 Answers2