[Answer]-Django Manager – Override the default get_query_set to set a default "GROUP BY"

by

1👍

✅

.count() actually creates a new query, with the fields removed and replaced with COUNT(*). It’s actually not possible to group by a field and do a count on the grouped table in plain SQL. Basically, your originally query looked like this in SQL:

SELECT myapp_variant.id, myapp_variant.name, myapp_variant.etc, ...
FROM myapp_variant inner join myapp_project on myapp_variant.project_id = myapp_project.id
WHERE myapp_project.name='zam'
GROUP BY myapp_variant.name

The count query looks something like this:

SELECT COUNT(*)
FROM myapp_variant inner join myapp_project on myapp_variant.project_id = myapp_project.id
WHERE myapp_project.name='zam'

Note that it can no longer group. If it did you would end up with the following resultset:

COUNT
-----
    4 
    1
    1
    1

(In this case, 4 is the number of RevA records, then 1 for each of the others)

Because when you group in an aggregate query, you are telling SQL to make a row for each unique value in each grouped column. 4 different variant names, so 4 records! This is not what you want at all

You can confirm whether this is the problem this by outputting the query that Django generates:

>>> print Variant.objects.filter(project__name__icontains="zam").group_by_name().query

>>> print Variant.objects.filter(project__name__icontains="zam").group_by_name().count().query

There are really only two solutions to this problem:

  1. Rewrite group_by_name so that instead of just grouping by a field, it actually returns a filtered queryset with just one record per name. Harder to do

  2. When you need a “count” for the grouped queryset, just use len() instead, as in 

    len(Variant.objects.filter(project__name__icontains="zam").group_by_name())

    or, in a template: 

    {{ grouped_variants|length }}
👤Jordan Reiter

Leave a comment