django count per column

user_1141095 0
Viewed 1847

I have a ORM like this

from django.db import models,

class MyObject(models.Model):

   class Meta:
       db_table = 'myobject'

   id = models.IntegerField(primary_key=True)
   name = models.CharField(max_length=48)                                        
   status = models.CharField(max_length=48)

Imagine I have the following entries

1 | foo | completed
2 | foo | completed
3 | bar | completed
4 | foo | failed

What is the django ORM query that I have to make in order to get a queryset somewhat like the following

[{'name': 'foo', 'status_count': 'completed: 2, failed: 1'},
 {'name': 'bar', 'status_count': 'completed: 1'}]

I started with the following but I don't know how to "merge" the two columns:

from django.db.models import Count
models.MyObject.objects.values(
    'name',
    'status'
).annotate(my_count=Count('id'))

The goal of all this to get a table where I can show something like the following:

Name | completed | failed
foo  | 2         | 1
bar  | 1         | 0
django-orm django-models django python
2 Answers

This should work as expected:

test = MyObject.objects.values('name').annotate(
    total_completed=Count(
        Case(
            When(
                status='completed', then=1), output_field=DecimalField()
        )
    ),
    total_failed=Count(
        Case(
            When(status='failed', then=1), output_field=DecimalField()
        )
    )
)
user_88276240

You need to include an "order_by" on to the end of your query to group the like items together.

Something like this should work:

from django.db.models import Count
models.MyObject.objects.values(
    'name',
    'status'
).annotate(my_count=Count('id')).order_by()

See https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#interaction-with-default-ordering-or-order-by for details.

EDIT: Sorry, I realize this doesn't answer the question about merging the columns... I don't think you can actually do it in a single query, although you can then loop through the results pretty easily and make your output table.

user_74707650
Related