CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-orm
ezdazuzena
ezdazuzena

Reputation: 6770

django count per column

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

Upvotes: 3

Views: 2076

Answers (2)

Borut
Borut

Reputation: 3364

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

Upvotes: 6

skulegirl
skulegirl

Reputation: 391

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.

Upvotes: 0

Related Questions