4

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.

Milano
  • 18,048
  • 37
  • 153
  • 353

1 Answers1

10

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.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • Thanks it works. `items for which it can not find a Model will be returned as None.` - the status field is a charfield, not FK so it should always find a Model. – Milano Jan 03 '21 at 15:36
  • @Milano: what I mean is that if there are *no* models with `status='active'`, then `{ 'nactive': None}`, it is possible that for example all your `Model` records have `status='draft'`, such that no items are active/cancelled. – Willem Van Onsem Jan 03 '21 at 15:37