Django get all values Group By particular one field

Question

I want to execute a simple query like:

    select *,count('id') from menu_permission group by menu_id

In Django format I have tried:

    MenuPermission.objects.all().values('menu_id').annotate(Count('id))

It selects only menu_id. The executed query is:

    SELECT `menu_permission`.`menu_id`, COUNT(`menu_permission`.`id`) AS `id__count` FROM `menu_permission` GROUP BY `menu_permission`.`menu_id`

But I need other fields also. If I try:

   MenuPermission.objects.all().values('id','menu_id').annotate(Count('id))

It adds 'id' in group by condition.

  GROUP BY `menu_permission`.`id`

As a result I am not getting the expected result. How I can get all all fields in the output but group by a single one?

score 4 · Answer 1 · answered May 15 '20 at 12:25

You can try subqueries to do what you need.

In my case I have two tables: Item and Transaction where item_id links to Item

First, I prepare Transaction subquery with group by item_id where I sum all amount fields and mark item_id as pk for outer query.

per_item_total=Transaction.objects.values('item_id').annotate(total=Sum('amount')).filter(item_id=OuterRef('pk'))

Then I select all rows from item plus subquery result as total filed.

items_with_total=Item.objects.annotate(total=Subquery(per_item_total.values('total')))

This produces the following SQL:

SELECT `item`.`id`, {all other item fields}, 
(SELECT SUM(U0.`amount`) AS `total` FROM `transaction` U0 
WHERE U0.`item_id` = `item`.`id` GROUP BY U0.`item_id` ORDER BY NULL) AS `total` FROM `item`

+1 for the Subquery, didn't know about this. I used this in my query and boom it worked perfectly, thank you! — Leonardo Alves, Jan 08 '21 at 21:19

score 1 · Answer 2 · answered Jul 18 '20 at 08:13

You are trying to achieve this SQL:

select *, count('id') from menu_permission group by menu_id

But normally SQL requires that when a group by clause is used you only include those column names in the select that you are grouping by. This is not a django matter, but that's how SQL group by works.

The rows are grouped by those columns so those columns can be included in select and other columns can be aggregated if you want them to into a value. You can't include other columns directly as they may have more than one value (since the rows are grouped).

For example if you have a column called "permission_code", you could ask for an array of the values in the "permission_code" column when the rows are grouped by menu_id.

Depending on the SQL flavor you are using, this could be in PostgreSQL something like this:

select menu_id, array_agg(permission_code), count(id) from menu_permissions group by menu_id

Similary django queryset can be constructed for this.

Hopefully this helps, but if needed please share more about what you need to do and what your data models are.

"normally SQL requires that when a group by clause is used you only include those column names in the select that you are grouping by" this is simply not true. At least not in PostgreSQL. — Ariel, Jul 22 '21 at 10:19
Selecting multiple columns and grouping by only one is actually very common in SQL and is supported by every SQL database that I'm aware of. — Nostalg.io, Feb 12 '23 at 01:42

score 1 · Answer 3 · edited Sep 01 '21 at 08:42

The only way currently that it works as expected is to hve your query based on the model you want the GROUP BY to be based on. In your case it looks like you have a Menu model (menu_id field foreign key) so doing this would give you what you want and will allow getting other aggregate information from your MenuPermission model but will only group by the Menu.id field:

Menu.objects.annotate(perm_count=Count('menupermission__id')).values('perm_count')

Of course there is no need for the "annotate" intermediate step if all you want is that single count.

score 0 · Answer 4 · answered Aug 09 '16 at 11:08

0

query = MenuPermission.objects.values('menu_id').annotate(menu_id_count=Count('menu_id'))

You can check your SQL query by print(query.query)

answered Aug 09 '16 at 11:08

bysucpmeti

811
1
9
17

1

Its only give menu_id and menu_id count value. But I need all other fields @bysucpmeti – Md. Mahmud Hasan Aug 09 '16 at 11:15

Brian H. · Answer 5 · 2020-07-27T11:43:21.647

This solution doesn't work, all fields end up in the group by clause, leaving it here because it may still be useful to someone.

model_fields = queryset.model._meta.get_fields()
queryset = queryset.values('menu_id') \
            .annotate(
                count=Count('id'),
                **{field.name: F(field.name) for field in model_fields}
            )

What i'm doing is getting the list of fields of our model, and set up a dictionary with the field name as key and an F instance with the field name as a parameter.
When unpacked (the **) it gets interpreted as named arguments passed into the annotate function.

For example, if we had a "name" field on our model, this annotate call would end up being equal to this:

queryset = queryset.values('menu_id') \
            .annotate(
                count=Count('id'),
                name=F("name")
            )

Unfortunately, if you review the SQL this produces, you'll find that the GROUP_BY clause now includes all those extra fields. — mkoistinen, May 01 '20 at 21:10
interesting, I guess I didn't notice because i was already working with a pretty big dataset and ended up not needing it so I didn't look to much into it, I wonder if the concept could be used to get the desired result though. — Brian H., Jul 27 '20 at 11:42

score -3 · Answer 6 · answered Jan 26 '18 at 06:58

-3

you can use the following code:

 MenuPermission.objects.values('menu_id').annotate(Count('id)).values('field1', 'field2', 'field3'...)

answered Jan 26 '18 at 06:58

mango

15
4

Django get all values Group By particular one field

6 Answers6

Linked