CODEWITHSUNDEEP
pythonsqldjangopostgresql
ezdazuzena
ezdazuzena

Reputation: 6770

Django group_by argument depending on order_by

I'm struggling (again) with Django's annotate functionality where the actual SQL query is quite clear to me.

Goal:

I want to get the number of users with a certain let's say status (it could be just any column of the model).

Approach(es):

1) User.objects.values('status').annotate(count=Count('*'))

This results into the following SQL query

SELECT users_user.status, COUNT(*) as count
FROM users_user
GROUP BY users_user.id
ORDER BY usser_user.id ASC

However, this will give me a queryset of all users each "annotated" with the count value. This is kind of the behaviour I would have expected.

2) User.objects.values('status').annotate(count=Count('*')).order_by()

This results into the following SQL query

SELECT users_user.status, COUNT(*) as count
FROM users_user
GROUP BY users_user.status

No ORDER BY, and now the GROUP BY argument is the status column. This is not what I expected, but the result I was looking for.

Question: Why does Django's order_by() without any argument affect the SQL GROUP BY argument? (Or broader, why does the second approach "work"?)

Some details:

Upvotes: 1

Views: 260

Answers (1)

ivissani
ivissani

Reputation: 2664

This is explained here

Fields that are mentioned in the order_by() part of a queryset (or which are used in the default ordering on a model) are used when selecting the output data, even if they are not otherwise specified in the values() call.

Upvotes: 1

Related Questions