Django annotate - conditional account

Question

I have a model called "StoreItem" and a model called "QuoteItem". The QuoteItem element points to StoreItem.

I am trying to annotate the counter of the number of quotation items on the elements of the store, but with the conditions for applying to the elements of quotes.

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' is a collection of StoreItems requests. "this_week" is a list of dates submitted this week (the filter I'm trying to apply). After I do the work with dates, I want to add more filters to this conditional account, but let's start with this.

Anyway, what I get is more like a boolean - if there are Quote elements that match the condition, no matter how much I have, the counter will be 1. else, there will be 0.

It looks like Count(Case()) checking if any item exists, and if so, return 1, while I want it to iterate over all the quote items that point to the storage item, and count them if they correspond to the condition (individually).

How to do it?

Answer 1

You need to wrap everything in the Sum statement instead of Count (I find it a little strange that Count works in general):

 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() ) ) ) 

Basically this adds all 0 and 1 for the internal Case statement, resulting in a count of the number of matches.

Answer 2

I performed a similar task. For me, Sum on Case/When didn't work due to how many tables I joined (it was a way to count). It ended as follows:

 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 really had to add two Count together:

 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 associated with quoteitems either through itemgroup or directly.