Django aggregate and annotate to count by group - multiple levels

user_4255757 0
Viewed 179

I have a model that looks like:

class Foo(models.Model):
    bar = models.CharField(...)
    baz = models.CharField(...)

Foo(bar="a", baz="x")
Foo(bar="a", baz="x")
Foo(bar="a", baz="y")
Foo(bar="b", baz="y")

And I want a queryset that will return the largest count of unique (bar, baz) pairs for each bar:

[
    {"bar": "a", "baz": "x", "count": 2},
    {"bar": "b", "baz": "y", "count": 1},
]

Ideally this will all be done within a query, I've tried combinations of distinct, aggregate, annotate, to no avail and can't see how to do it other than run raw SQL which I want to avoid.

I'm using the PostgreSQL as my database backend.

postgresql django python
1 Answers

Django’s database-abstraction API is pretty powerful, and more often than not I have found it possible to achieve the necessary results for even the most complex queries. It can be confusing at first but you can achieve a lot with annotation when used correctly.

https://docs.djangoproject.com/en/4.0/topics/db/aggregation/#values

I was able to achieve the results with Django values, annotate and Count:

from django.db.models import Count
from .models import Foo

Foo.objects.all().values('bar','baz').annotate(count=Count('id')).order_by('-count')

Update The answer above misses the point that the question the result must return the largest count of unique (bar, baz) pairs for each bar.

Whilst I haven't been able to filter the below result, I was able to return a row_number key using the Window and RowNumber expressions. This can then be filtered in the template where row_number==1 but I don't think that would be the best approach.

from django.db.models import F, Count
from django.db.models.expressions import Window
from django.db.models.functions import RowNumber

Foo.objects.all().values('bar','baz')\
            .annotate(count=Count('id')).order_by('-count')\
            .annotate(
                row_number=Window(
                    expression=RowNumber(),
                    partition_by=[F('bar')],
                    order_by=F('count').desc()))
user_89190850
Related