Filtering within an annotate() query

Question

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)

You might want to add unique_together = ('comment', 'user') to CommentVote. Also, db_index on ForeignKeys is not necessary in MySQL and PostgreSQL. — Tomasz Zieliński
– Tomasz Zieliński, Commented Jan 24, 2012 at 10:27

Tomasz Zieliński · Accepted Answer · 2012-01-23 09:01:45Z

2

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.

answered Jan 23, 2012 at 9:01

Tomasz Zieliński

16.4k8 gold badges64 silver badges85 bronze badges

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

2 Comments

Tomasz Zieliński Over a year ago

Btw this is also now exactly what the OP needs, but I cannot make two annotations on the same related model work simulatnously, if one is "global" and the second one filtered..

Tomasz Zieliński Over a year ago

Argh, "now" should be "not" in the above comment

Sandip Agarwal · Accepted Answer · 2012-01-23 13:34:32Z

0

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',)

edited Jan 23, 2012 at 13:34

answered Jan 23, 2012 at 5:45

Sandip Agarwal

1,9205 gold badges28 silver badges42 bronze badges

10 Comments

Sandip Agarwal Over a year ago

Could I please know the mistake that I have committed in this answer?

Tomasz Zieliński Over a year ago

According to me it just doesn't do what you advertise it should do. But if you prove that it works then I'll happily reverse the vote.

Sandip Agarwal Over a year ago

Hey Tomasz, I have found a possible mistake. The filter should be placed before the annotate. Is there anymore mistakes?

Tomasz Zieliński Over a year ago

Your query is now equivalent to: CommentVote.objects.filter(user=user, comment__video_id=video_id).values_list('comment', 'value'), i.e. it filters votes by given user for given video and returns comment_id and value (disguised as vote_sum) for them.

Sandip Agarwal Over a year ago

I think (and it works as well) that the annotate would work based on values('comment') for the particular video_id and user. values_list is next in the process. Please correct me, if I am wrong anywhere.

|

Collectives™ on Stack Overflow

Filtering within an annotate() query

2 Answers 2

2 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related