Django annotation multiply fields

Question

When I try to annotate my model I face an issue that two fields multiply each other

def get_queryset(self):
return self.queryset.annotate(
    my_votes=Count("votes", filter=Q(votes=self.request.user), distinct=False)
    vote_count=Count("votes", distinct=False)
    comments_count=Count("comments", distinct=True)
)

I know that there is an issue with multiple aggregations

Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries

django docomentation

For example, if there are 3 comments on the post and I voted on the post 4 times and there are 7 votes total the count will return:

my_votes=12 (4 my_votes * 3 comments)

vote_count=21 (7 votes * 3 comments)

comments_count=3

if I remove the comments count everything works as it should.

if there are 0 comments it also counts properly

There are ManyToMany relations between Post and Vote

The is ForignKey between Comment and Post

Is there another way to accomplish this?

Thank you for your help.

duplicate of https://stackoverflow.com/q/6795202? – djvg Feb 14 '23 at 10:53 — djvg, Feb 14 '23 at 10:53

score 1 · Accepted Answer · answered Aug 30 '22 at 13:57

If just setting distinct=True doesn't work, then you can use subqueries, as shown in Serafim's answer here. Given that you mention Vote and Post are a many-to-many relation, you could rewrite e.g. my_votes as a subquery on the through table, something like:

def get_queryset(self):
    my_votes_sq = Subquery(
        Vote.posts.through.objects
         .filter(post_id=OuterRef("pk"), user_id=self.request.user.id)
         .order_by()
         .values("post_id")
         .annotate(count=Count("pk"))
         .values("count"), output_field=IntegerField()
    )
    ...
    return self.queryset.annotate(
        my_votes=Coalesce(my_votes_sq, 0),
        ...
    )

The actual field names for posts, post_id, user_id will depend on your models of course.

Django annotation multiply fields

1 Answers1