I have list of Demand
objects that have allocated
field that would either be null
or have a name (denoting this demand's allocation).
I use annotations to count allocated/unallocated numbers per team:
Demand.objects.filter(project=project).values('team').annotate(
unallocated=Count('allocated', filter=Q(allocated__isnull=True)),
allocated=Count('allocated', filter=Q(allocated__isnull=False))
)
What's weird is that the numbers for the allocated
annotation come out right, but the numbers for the unallocated
are always zero.
For instance:
list(Demand.objects.filter(project=project).values('allocated', 'team'))
With the following outcome:
[{'allocated': None, 'team': 'Design'},
{'allocated': None, 'team': 'Engineering'},
{'allocated': None, 'team': 'Engineering'},
{'allocated': None, 'team': 'Engineering'},
{'allocated': None, 'team': 'Delivery'},
{'allocated': None, 'team': 'Product'}]
but the annotations with have just this:
<QuerySet
[{'team': 'Delivery', 'unallocated': 0, 'allocated': 0},
{'team': 'Design', 'unallocated': 0, 'allocated': 0},
{'team': 'Engineering', 'unallocated': 0, 'allocated': 0},
{'team': 'Product', 'unallocated': 0, 'allocated': 0}]>
Am I doing it wrong or it may be a bug?
That is because Count(…)
[Django-doc] does not count NULL
, that is how SQL specifies how a COUNT
aggregate works: it does not consider NULL
values (this is also the case for AVG
for example). But you can instead count the primary key for example:
from django.db.models import Count, Q
Demand.objects.filter(project=project).values('team').annotate(
unallocated=Count('pk', filter=Q(allocated=None)),
allocated=Count('allocated', filter=Q(allocated__isnull=False))
).order_by('team')
Therefore, you can also simplify allocated
to:
from django.db.models import Count, Q
Demand.objects.filter(project=project).values('team').annotate(
unallocated=Count('pk', filter=Q(allocated=None)),
allocated=Count('allocated')
).order_by('team')