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
– gallen, Commented Feb 13, 2017 at 7: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
– Fahim Ahmed, Commented Feb 13, 2017 at 7:46

Piyush S. Wanare · Accepted Answer · 2017-02-13 07:52:03Z

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, 2017 at 7:52

Piyush S. Wanare

4,9836 gold badges41 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fahim Ahmed Over a year ago

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

Community · Accepted Answer · 2017-05-23 11:53:13Z

1

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

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Feb 13, 2017 at 7:42

Fahim Ahmed

4076 silver badges16 bronze badges

1 Comment

elke Over a year ago

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 · Accepted Answer · 2018-04-03 14:25:10Z

0

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.

answered Apr 3, 2018 at 14:25

elke

1,3202 gold badges12 silver badges25 bronze badges

Comments

Fran Lendínez · Accepted Answer · 2020-02-27 17:18:13Z

0

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

edited Feb 27, 2020 at 17:18

answered Feb 27, 2020 at 16:52

Fran Lendínez

3642 silver badges10 bronze badges

Collectives™ on Stack Overflow

Django query to list the count of field values distinctly

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related