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?

Answer 1

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` 

Answer 2

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.

Answer 3

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.