How filter annotated data in django-queryset

Question

I want to calculate count of approved comments?

news_list = News.objects.all()\
    .annotate(
        comments_count=Count(
            'comments__id', 
            comments__status=COMMENT_STATUS_APPROVED
        )
    )

But the second condition of Count-function is not working. How to filter annotate-function

Filtering on annotations is not possible in Django. You will have to use custom SQL. This blog entry may be useful: timmyomahony.com/blog/filtering-annotations-django — Leistungsabfall
– Leistungsabfall, Commented Feb 15, 2016 at 10:58
@Leistungsabfall Thanks for this link! I was changed the my queryset to use extra-select. — Dmitry
– Dmitry, Commented Feb 15, 2016 at 11:32

Community · Accepted Answer · 2017-05-23 12:31:08Z

2

There is a similar question on how to annotate a count with a condition with a detailed answer and explanation of the SQL.

You can do conditional aggregation, using the conditional expression Case. The example in the docs is shows operation on a single model, but you can use the normal methods for inter-model relationships. The following QuerySet should be what you're looking for-

class NewsQuerySet(models.QuerySet):
    def with_comment_counts(self):
        query = self
        query = query.annotate(
            comment_count=Sum(
                Case(When(comment__status=COMMENT_STATUS_APPROVED, then=1
                          default=0,
                          output_field=IntegerField())
            ),
        return query

edited May 23, 2017 at 12:31

CommunityBot

11 silver badge

answered Feb 16, 2016 at 16:37

Linville

3,8031 gold badge32 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

How filter annotated data in django-queryset

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related