CODEWITHSUNDEEP
mysqldjangoannotations
Ed.
Ed.

Reputation: 4577

Django conditional count

I am querying the names of all the Tags that I've flagged as "visible":

visible_tags = Tag.objects.filter(visible=True,taggit_taggeditem_items__content_type=ContentType.objects.get_for_model(Action)).order_by('name')

I want to add a field called "action_count" that tells me how many actions are associated with this tag:

visible_tags = Tag.objects.filter(visible=True,taggit_taggeditem_items__content_type=ContentType.objects.get_for_model(Action)).order_by('name').annotate(action_count=Count('action'))

This works except that I want to know not just now many actions are affiliated, but how many actions that are incomplete that are affiliated with this tag.

I tried the following:

visible_tags = Tag.objects.filter(visible=True,taggit_taggeditem_items__content_type=ContentType.objects.get_for_model(Action)).order_by('name').filter(action__complete=False).annotate(action_count=Count('action'))

But this doesn't quite do what I need it to do. How can I annotate the count of actions that are incomplete?

Upvotes: 1

Views: 299

Answers (1)

okm
okm

Reputation: 23871

You might get more count than expected. This is because chained .filter that introduce extra inner join, very similar to the question Are chained QuerySet filters equivalent to defining multiple fields in a single filter with the Django ORM? Thus put the second filter in the first one:

visible_tags = Tag.objects.filter(visible=True, taggit_taggeditem_items__content_type=ContentType.objects.get_for_model(Action), 
action__complete=False # Here
).order_by('name').annotate(action_count=Count('action'))

Besides, print queryset.query to know what SQL Django generates for you.

Upvotes: 1

Related Questions