CODEWITHSUNDEEP
djangodjango-orm
Jackie
Jackie

Reputation: 13

How do I calculate a set of answers' scores in one query?

I am writing a webapp similar to Stackoverflow. How can I query Questions and annotate each question with its score, which is simply how many upvotes it's has minus how many downvotes it's had.

class Question(models.Model):
    pass

class Answer(models.Model):
    pass

VOTE_CHOICES = (
    ('U', 'Up'),
    ('D', 'Down'),
)

class Vote(models.Model):
    user = models.ForeignKey(User)
    answer = models.ForeignKey(Answer)
    type = models.CharField(max_length=1, choices=VOTE_CHOICES, db_index=True) 

    class Meta:
        unique_together = (("user", "answer"),)

Upvotes: 1

Views: 72

Answers (2)

berni
berni

Reputation: 1975

Although DrTyrsa answer is 100% correct. You can use below if there is a real need for VOTE_CHOICES to be 'U' and 'D'.

votes = Vote.objects.filter(answer__question=q)
rank = votes.filter(type='U').count() - votes.filter(type='D').count()

Upvotes: 0

DrTyrsa
DrTyrsa

Reputation: 31951

VOTE_CHOICES = (
    (1, 'Up'),
    (-1, 'Down'),
)

will make it much easier:

# q - your question
Vote.objects.filter(answer__question=q).aggregate(Sum('type'))

# all questions annotated
Vote.objects.values('answer__question_id').annotate(score=Sum('type')).order_by()

Upvotes: 1

Related Questions