How can I use annotate() to count a subset of related models in Django?

Question

I'm trying to use Django's annotate feature to add the count of a related model to a queryset. However, I don't want a full count of related objects, I only want to count the active ones (i.e., "is_active=True"). I can't figure out how to filter down the count.

The (simplified) relevant models:

class Post(models.Model):
    user = models.ForeignKey(User)
    title = models.CharField(max_length=80)
    body = models.TextField()

class Comment(models.Model):
    user = models.ForeignKey(User)
    post = models.ForeignKey(Post)
    comment_body = models.CharField(max_length=80)
    is_active = models.BooleanField(default=True)

In a view, I'm trying to annotate a queryset:

queryset=Post.objects.all().annotate(num_comments=Count('comment', distinct=True))

The above counts all the comments related to a post, whereas I only want to count the "is_active" ones. Google and the Django docs aren't helping me here. Has anyone had and solved this problem?

Thanks!

Answer 1

You just need to filter on is_active before doing the annotation:

Post.objects.filter(comment__is_active=True).annotate(num_comments=Count('comment'))

See the explanation here.

Answer 2

This is how I had to "annotate" the number of active comments on my Post queryset:

Post.objects.extra(select={"num_comments":
     """
     SELECT COUNT(myapp_comment.id) FROM myapp_reply
     WHERE myapp_comment.is_active='1' AND 
     myapp_comment.post_id = myapp_post.id
     """
     },)

Not pretty, but it works. As I mentioned in a comment above, it wasn't possible to use the built-in aggregation function annotate() for this, since that counted all related comments and I only wanted to count the active related comments.

Daniel's solution didn't work, because it filtered out Posts which had no comments. I don't want to filter out any Posts, just inactive comments.

If anyone has a better solution, I will gladly up-vote and best-answer you!