CODEWITHSUNDEEP
pythondjangocountannotate
Fahim Ahmed
Fahim Ahmed

Reputation: 407

Django query to list the count of field values distinctly

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

Upvotes: 2

Views: 5289

Answers (4)

Fran Lendínez
Fran Lendínez

Reputation: 364

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

Upvotes: 0

elke
elke

Reputation: 1270

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.

Upvotes: 0

Piyush S. Wanare
Piyush S. Wanare

Reputation: 4933

This will work for you :-

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

Upvotes: 3

Fahim Ahmed
Fahim Ahmed

Reputation: 407

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

Upvotes: 1

Related Questions