GROUP BY and HAVING

Django Internals
1

Are there any specific reasons that Django does not have an API / function to specify group by and having statements manually?

I usually have a decent grasp on the exact SQL query to run, but doing it with the Django ORM tends to give me a migraine as there is the values() stmt followed by the fk’s value

I usually fall back to Raw SQL, but having a simple way to define these values would fix my biggest gripe with ORM.

2 Likes
2

I agree it can be confusing to newcomers… but… there are a few things to point out:

What is it exactly that you are trying to achieve, like with a specific example? The ORM is literally an Object-Relation map. Group by will aggregate relations into new relation types which no longer map to your defined objects (models).

This is what values() is for – to map to dictionaries (or tuples) instead.

So that’s why values() will do grouping for you when you annotate() an aggregation expression.

Therefore the pattern you want is queryset.values(<group by keys>).annotate(<aggregate expression>).

There has been some talk about explicitly providing a group_by() function here and on the old ML, but honestly once you understand this pattern it seems no longer relevant.

If you wish to understand how Django works and why this makes sense read my “stupid Django trick” on group by: stupid-django-tricks/explicit_group_by at master · shangxiao/stupid-django-tricks · GitHub Note/Disclaimer: that trick is a draft WIP so only the explanation at the top is worth reading, the trick below that is unfinished.

5 Likes
3

We often have to run queries like these

            select distinct agency_id from demande_statistics ds 
            where ds.demande_month in (%s , %s)
            group by agency_id having count(*) filter (where status = 'FINANCED') = 0;

Then I need to use this as a CTE for another bunch of joins. (not to mention getting a left or cross join to work)

I’m not sure how one can do this easily with the ORM, but the query builder having a DSL that is not close to std SQL for basic features like Group by / having and needing to tinker around to get what I already I know i need, is my biggest gripe with the ORM in my last 3 years of creating and maintaining Django projects

4

By-the-by @shangxiao, that is an excellent answer :top_hat:

2 Likes