Annotating a queryset using aggregations of values with more than one field

user_12137312 0
Viewed 1063

Django annotations are really awesome. However I can't figure out how to deal with annotations where several values() are required.

Question:

I would like to annotate an author_queryset with counts of items in a related m2m. I don't know if I need to use a Subquery or not, however:

annotated_queryset = author_queryset.annotate(genre_counts=Subquery(genre_counts))

Returns:

SyntaxError: subquery must return only one column

I've tried casting the values to a JSONField to get it back in one field, hoping that I could use JSONBagg on it since I'm using postgres and need to filter the result:

subquery = Author.objects.filter(id=OuterRef('pk')).values('id','main_books__genre').annotate(genre_counts=Count('main_books__genre'))

qss = qs.annotate(genre_counts=Subquery(Cast(subquery,JSONField()), output_field=JSONField()))

Yeilds:

AttributeError: 'Cast' object has no attribute 'clone'

I'm not sure what I need to get the dict cast to a JSONField(). There's some great info here about filtering on these. There's also something for postgres coming soon in the development version called ArraySubQuery() which looks to address this. However, I can't use this feature until it's in a stable release.

Desired result

I would like to annotate so I could filter based on the annotations, like this:

annotated_queryset.filter(genre_counts__scifi__gte=5)

Detail

I can use dunders to get at a related field, then count like so:

# get all the authors with Virginia in their name
author_queryset = Author.objects.filter(name__icontains='Virginia')
author_queryset.count()
# returns: 21

# aggregate the book counts by genre in the Book m2m model
genre_counts = author_queryset.values('id','main_books__genre').annotate(genre_counts=Count('main_books__genre'))
genre_counts.count()

# returns: 25

this is because there can be several genres counts returned for each Author object in the queryset. In this particular example, there is an Author which has books in 4 different genres:

To illustrate:

...
{'id': 'authorid:0054f04', 'main_books__genre': 'scifi', 'genre_counts': 1}
{'id': 'authorid:c245457', 'main_books__genre': 'fantasy', 'genre_counts': 4}
{'id': 'authorid:a129a73', 'main_books__genre': None, 'genre_counts': 0}
{'id': 'authorid:f41f14b', 'main_books__genre': 'mystery', 'genre_counts': 16}
{'id': 'authorid:f41f14b', 'main_books__genre': 'romance', 'genre_counts': 1}
{'id': 'authorid:f41f14b', 'main_books__genre': 'scifi', 'genre_counts': 9}
{'id': 'authorid:f41f14b', 'main_books__genre': 'fantasy', 'genre_counts': 3}
...

and there is another Author with 2, the rest have one genre each. Which is the 25 values.

Hoping this makes sense to someone! I'm sure there is a way to handle this properly without waiting for the feature described above.

django-queryset postgresql django-models django python
1 Answers

You want to use .annotate( without a Subquery because as you found, that needs to return a single value. You should be able to span all the relationships in the first annotate's count expression.

Unfortunately Django doesn't support what you're looking for with genre_counts__scifi_gt=5 currently. You can structure it such that you do the Count with the filter passed to it.

selected_genre = 'scifi'
annotated_queryset = author_queryset.annotate(
    genre_count=Count("main_books__genre", filter=Q(genre=selected_genre))
).filter(genre_count__gte=5)

To have the full breakdown, you'll be better off returning the breakdown and doing the final aggregation in the application as you showed in your question.

user_16373510
Related