django annotate - conditional count

Question

I have a model called 'StoreItem' and a model named 'QuoteItem'. A QuoteItem points on a StoreItem.

I'm trying to annotate a counter of how many quote items point on store items, but with conditions to apply on the quote items.

I tried something like this:

items = items.annotate(
            quote_count=Count(
                Case(
                    When(quoteitem__lookup_date__in=this_week, then=1), 
                    output_field=IntegerField()
                )
            )
        )

'items' are a queryset of StoreItems. 'this_week' is a list of dates representing this week (that's the filter I try to apply). After I make the dates thing work I want to add more filters to this conditional count but lets start with that.

Anyway what I'm getting is more like a boolean - if Quote Items that match the condition exists, no matter how many I have, the counter will be 1. else, will be 0.

It looks like the Count(Case()) only check if any item exist and if so return 1, while I want it to iterate over all quote items that point on the store item and count them, if they match the condition (individually).

How do I make it happen?

score 15 · Accepted Answer · answered Jun 29 '16 at 03:55

You need to wrap everything in a Sum statement instead of Count (I find it a bit odd that Count works at all):

from django.db.models import Case, IntegerField, Sum, When

items = items.annotate(
        quote_count=Sum(
            Case(
                When(quoteitem__lookup_date__in=this_week, then=1), 
                output_field=IntegerField()
            )
        )
    )

This basically adds up all the 0s and 1s for the inner Case statement, resulting in a count of the number of matches.

score 0 · Answer 2 · answered Nov 28 '17 at 15:12

I was doing a similar task. For me, Sum over the Case/When was not working due to how many tables I was joining (it was way over counting). Ended up like this:

from django.db.models import Case, IntegerField, Count, When, F

items = items.annotate(
        quote_count=Count(
            Case(
                When(quoteitem__lookup_date__in=this_week, then=F('quoteitem__id'), 
            ),
            distinct=True,
        )
    )

In my case I actually had to add two Counts together like:

items = items.annotate(
        quote_count=Count(
            Case(
                When(quoteitem__lookup_date__in=this_week, then=F('quoteitem__id'), 
            ),
            distinct=True,
        )
    ) + Count (
            Case(
                When(itemgroup__lookup_date__in=this_week, then=F('itemgroup__quoteitem__id'), 
            ),
            distinct=True,
        )

Assuming that items can be related to quoteitems either through an itemgroup or directly.

django annotate - conditional count

2 Answers2