CODEWITHSUNDEEP
djangodjango-modelsdjango-queryset
Bridger Maxwell
Bridger Maxwell

Reputation: 2119

Filtering within an annotate() query

I am making a django app with comments and voting on those comments, in the stackoverflow or reddit style. When selecting the comments, I would like to know both the aggregate vote count, and whether the user has voted on this particular comment. I can use annotate for the aggregate count like so:

video_comments = Comment.objects.filter(video_id=video_id).annotate(vote_sum=Sum('commentvote__value'))

Can I also annotate a subset of the commentvotes? Something like:

.annotate(user_vote=Sum('commentvote__value').filter(commentvote__user == user))

For reference, this is my model:

class Comment(models.Model):
    video_id = models.CharField(max_length=12, db_index=True)
    video_time = models.FloatField()

    comment = models.TextField(max_length=MAX_COMMENT_LENGTH)

    datetime = models.DateTimeField(auto_now_add = True)
    user = models.ForeignKey(User)

class CommentVote(models.Model):
    comment = models.ForeignKey(Comment, db_index=True)
    value = models.IntegerField() # Should be 1 or -1

    datetime = models.DateTimeField(auto_now_add = True)
    user = models.ForeignKey(User, db_index=True)

Upvotes: 2

Views: 1013

Answers (2)

Sandip Agarwal
Sandip Agarwal

Reputation: 1910

I think this should give you what you require:

CommentVote.objects.values('comment').filter(comment__video_id=video_id,user=user).annotate(vote_sum=Sum('value')).values_list('comment','vote_sum',)

Upvotes: 0

Tomasz Zieliński
Tomasz Zieliński

Reputation: 16377

According to this, you can filter on a given field before annotating:

Comment.objects.filter(video_id=video_id).filter(commentvote__user=user))\
               .annotate(user_vote=Sum('commentvote__value'))

Unfortunately this narrows the comment set to just those comments which have vote(s) cast by given user. But you can also get the remaining comments:

Comment.objects.filter(video_id=video_id).exclude(commentvote__user=user))

and combine both lists manually.

Upvotes: 2

Related Questions