django count per column

Question

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

Answer 1

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