Django - annotate with multiple Count

Question

I have a model called Post which has two fields upvotes and downvotes. Now, upvotes, downvotes are ManyToManyField to a Profile. This is the model:

class Post(models.Model):
    profile = models.ForeignKey(Profile, on_delete=models.CASCADE)
    title = models.CharField(max_length=300)
    content = models.CharField(max_length=1000)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    subreddit = models.ForeignKey(Subreddit, on_delete=models.CASCADE)
    upvotes = models.ManyToManyField(Profile, blank=True, related_name='upvoted_posts')
    downvotes = models.ManyToManyField(Profile, blank=True, related_name='downvoted_posts')

So, I want to fetch all the posts such that they are in the order of

total(upvotes) - total(downvotes)

So I have used this query:

Post.objects.annotate(
    total_votes=Count('upvotes')-Count('downvotes')
).order_by('total_votes')

The problem with this query is the total_votes is always turning out to be zero.

The below queries will explain the situation:

In [5]: Post.objects.annotate(up=Count('upvotes')).values('up')
Out[5]: <QuerySet [{'up': 1}, {'up': 3}, {'up': 2}]>

In [6]: Post.objects.annotate(down=Count('downvotes')).values('down')
Out[6]: <QuerySet [{'down': 1}, {'down': 1}, {'down': 1}]>

In [10]: Post.objects.annotate(up=Count('upvotes'), down=Count('downvotes'), total=Count('upvotes')-Count('downvotes')).values('up', 'down', 'total')
Out[10]: <QuerySet [{'up': 1, 'down': 1, 'total': 0}, {'up': 3, 'down': 3, 'total': 0}, {'up': 2, 'down': 2, 'total': 0}]>

Seems like both up and down are having the same value(which is actually the value of up). How can I solve this?

I have tried this:

In [9]: Post.objects.annotate(up=Count('upvotes')).annotate(down=Count('downvotes')).values('up', 'down')
Out[9]: <QuerySet [{'up': 1, 'down': 1}, {'up': 3, 'down': 3}, {'up': 2, 'down': 2}]>

but even this gives the same output.

Answer 1

Try to use dictinct argument:

Post.objects.annotate(
    total_votes=Count('upvotes', distinct=True)-Count('downvotes', distinct=True)
).order_by('total_votes')

From the docs:

Combining multiple aggregations with annotate() will yield the wrong results because joins are used instead of subqueries. For most aggregates, there is no way to avoid this problem, however, the Count aggregate has a distinct parameter that may help.