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.

Wouldn't it make more sense to have a many-to-many to a profile, and store in the m2m table whether it is an upvote/downvote? — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Aug 6, 2018 at 6:44
Yes, It is a ManyToMany with Profile and I think Django handles that table that you are talking about!! — Sreekar Mouli
– Sreekar Mouli, Commented Aug 6, 2018 at 7:21
correct, but you can define the through table, and thus add attributes to the relation: docs.djangoproject.com/en/2.1/ref/models/fields/… — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Aug 6, 2018 at 7:25

neverwalkaloner · Accepted Answer · 2018-08-06 05:37:14Z

14

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.

answered Aug 6, 2018 at 5:37

neverwalkaloner

47.6k7 gold badges101 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AKX · Accepted Answer · 2018-08-06 07:27:22Z

(I'm aware that this isn't exactly an answer, but code can't be embedded in a comment.)

A better data model would be

class Post:
  # ...

class Vote:
  voter = models.ForeignKey(Profile, on_delete=models.PROTECT)
  post = models.ForeignKey(Post, on_delete=models.CASCADE)
  score = models.IntegerField()  # either -1 or +1; validate accordingly

  class Meta:
    unique_together = [('voter', 'post'),]

This way you could count the current total score for a post simply with

Vote.objects.filter(post=post).aggregate(score=Sum('score'))

However, you should be well aware of the performance implications of doing this (or your original version for that matter) every time. It would be better to add a

score = models.IntegerField(editable=False)

field to the Post, that gets updated with the aggregate score every time a vote is created, modified or deleted.

Collectives™ on Stack Overflow

Django - annotate with multiple Count

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related