How to filter/exclude inactive comments from my annotated Django query?

Question

I'm using the object_list generic view to quickly list a set of Articles. Each Article has comments attached to it. The query uses an annotation to Count() the number of comments and then order_by() that annotated number.

'queryset': Article.objects.annotate(comment_count=Count('comments')).order_by('-comment_count'),

The comments are part of the django.contrib.comments framework and are attached to the model via a Generic Relationship. I've added an explicit reverse lookup to my Article model:

class Article(models.Models):
   ...
   comments = generic.GenericRelation(Comment, content_type_field='content_type', object_id_field='object_pk')

The problem is, this counts "inactive" comments; ones that have is_public=False or is_removed=True. How can I exclude any inactive comments from being counted?

Answer 1

The documentation for aggregations explains how to do this. You need to use a filter clause, making sure you put it after the annotate clause:

Article.objects.annotate(comment_count=Count('comments')).filter(
     comment__is_public=True, comment__is_removed=False
).order_by('-comment_count')