CODEWITHSUNDEEP
djangodjango-modelsannotate
mitchf
mitchf

Reputation: 3807

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

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?

Upvotes: 5

Views: 2865

Answers (3)

Venelin Stoykov
Venelin Stoykov

Reputation: 184

There is two variants based on what database you are using. If you use MySQL the solution is simple and elegant:

Post.objects.annotate(num_comments=Sum('comment.is_active'))

This works because in database boolean fields are integers and True is 1, False is 0.

But this works ONLY in MySQL and works ONLY for boolean fields. The more general way to do the job which works on all databases and can do more complex checks is using a little SQL inserted a little 'hacky':

Post.objects.annotate(num_comments=Count('comment',
    field='CASE WHEN myapp_comment.is_active THEN 1 END'))

I have the same problem in my personal blog, and that was the solution. I write a blog post for that. http://venelin.sytes.net/blog/django/filtrirane-na-agregirash-count-v-django/. It's on bulgarian but my site uses google translation. The translation is not very good but may help to understand why this works.

Upvotes: 3

mitchf
mitchf

Reputation: 3807

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!

Upvotes: 3

Daniel Roseman
Daniel Roseman

Reputation: 599600

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.

Upvotes: 5

Related Questions