Django query to list the count of field values distinctly

Question

I have a model which has a field named 'state'. It has values like 'completed', 'in_progress', 'failed', 'created'. I need to know if I can write a django query which will through me a list like

{'completed': 2, 'in_progress': 5, 'failed': 0, 'created': 2}

for the table values

id | order_id | state
---------------------------
1  | 23       | completed
2  | 23       | completed
3  | 23       | in_progress
4  | 23       | created
5  | 23       | created
6  | 23       | in_progress
7  | 23       | in_progress
8  | 23       | in_progress
9  | 23       | in_progress

I tried running the below query

order_items = OrderItems.objects.filter(order=order)
order_states = order_items.filter(
    state__in=['in_progress', 'completed', 'failed', 'created']
).values('state').annotate(Count('state'))

But it gave me a list which came up like this

[{'state': u'completed', 'state__count': 8}, 
{'state': u'failed', 'state__count': 1}, 
{'state': u'in_progress', 'state__count': 1}]

Your desired output is not a list, it is a dictionary. All Django queries, so far as I know, will return some form of iterable structure, usually a QuerySet, which behaves much like a list of dictionaries, or an instance of the model being queried for. You could loop over your resulted list and create the desired dictionary, but depending on your data set size that may be inefficient. — gallen, Feb 13 '17 at 07:33
Yes, that was the reason I needed a queryset that would allow me to avoid the looping since the data size will be huge — Fahim Ahmed, Feb 13 '17 at 07:46

score 3 · Answer 1 · answered Feb 13 '17 at 07:52

3

This will work for you :-

from django.db.models import Count
StateStatusCount = order_items.objects.values('state').annotate(the_count=Count('state'))
print StateStatusCount

answered Feb 13 '17 at 07:52

Piyush S. Wanare

4,703
6
37
54

This is what I was doing which did not get me the correct output. Check my answer. That returned the correct result. Thanks for your effort – Fahim Ahmed Feb 13 '17 at 08:35

score 1 · Answer 2 · edited May 23 '17 at 11:53

Guys i just found it out myself. Since my django version is outdated I used the aggregate_if which is better written in the latest version as conditional expressions.

from django.db.models import Q
from aggregate_if import Count, Sum

order_states = order_items.aggregate(
    created=Count('pk', only=Q(state=OrderItems.STATE_CREATED)),
    in_progress=Count('pk', only=Q(state=OrderItems.STATE_IN_PROGRESS)),
    complete=Count('pk', only=Q(state=OrderItems.STATE_COMPLETED)),
    failed=Count('pk', only=Q(state=OrderItems.STATE_FAILED))
)

where

OrderItems.STATE_CREATED = 'created'
OrderItems.STATE_IN_PROGRESS = 'in_progress'
OrderItems.STATE_COMPLETED = 'completed'
OrderItems.STATE_FAILED = 'failed'

Thanks to @shahanar for helping me figuring out this

For many applications, it would be neat if this answer was generic, i.e. you wouldn't have to explicitly list the possible states. — elke, Apr 03 '18 at 13:57

score 0 · Answer 3 · answered Apr 03 '18 at 14:25

To get exactly the dictionary structure that Fahim requires, we can extend Piyush's reply with a dict comprehension to:

from django.db.models import Count
state_count = {i["state"]: i["count"] for i in order_items.objects.values('state').order_by().annotate(count=Count('state'))}
print(state_count)

Further, order_by() is needed e.g. if an order is set the the Model's Meta class. For an excellent explanation, please see Django equivalent of COUNT with GROUP BY.

Fran Lendínez · Answer 4 · 2020-02-27T17:18:13.477

In addition to https://stackoverflow.com/a/42198898/7030141

You can dynamically generate per state COUNT using kwargs:

class OrderItem(models.Model):
    STATE_CHOICES = (
        ('a', _('a')),
        ('b', _('b')),
        ...
    )
    order = models.ForeignKey...
    state = models.CharField(choices...

from django.db.models import Q, Count

order_items.aggregate(
    **{status: Count('pk', filter=Q(state=status)) for status, _ in OrderItem.STATE_CHOICES}
)

Django query to list the count of field values distinctly

4 Answers4