Django - Query count of each distinct status

Question

I have a model Model that has Model.status field. The status field can be of value draft, active or cancelled.

Is it possible to get a count of all objects based on their status? I would prefer to do that in one query instead of this:

Model.objects.filter(status='draft').count()
Model.objects.filter(status='active').count()
Model.objects.filter(status='cancelled').count()

I think that aggregate could help.

Answer 1

Yes, you can work with:

from django.db.models import Count

Model.objects.values('status').annotate(
    count=Count('pk')
).order_by('count')

This will return a QuerSet of dictionaries:

<QuerySet [
    {'status': 'active', 'count': 25 },
    {'status': 'cancelled', 'count': 14 },
    {'status': 'draft', 'count': 13 }
]>

This will however not list statuses for which no Model is present in the database.

Or you can make use of an aggregate with filter=:

from django.db.models import Count, Q

Model.objects.aggregate(
    nactive=Count('pk', filter=Q(status='active')),
    ncancelled=Count('pk', filter=Q(status='cancelled')),
    ndraft=Count('pk', filter=Q(status='draft'))
)

This will return a dictionary:

{
    'nactive': 25,
    'ncancelled': 25,
    'ndraft': 13
}

items for which it can not find a Model will be returned as None.