Conditional annotations in Django

Question

I got simple requirement (not simple implementation), and figuring out how to achieve it without making multiple hits to db, and without .extra() in queryset.

Task:
  name = xxx
  status = models.IntegerField(choices=some_choices)
  project = ForeignKey(Project)

Project:
  name = xxx
  code = xxx

Projects contain Tasks which got various statuses. (Assume status=3 is Completed) Now, I want to list out all projects with their total tasks and completed tasks, like below

1. Project 1, total_tasks=5, completed_tasks=2
2. Project 1, total_tasks=2, completed_tasks=1

I am able to get total_tasks with annotate, but not completed_tasks, since it required condition in annotation. Is there anyway to do it?

Answer 1

This feature is new in Django 1.8.

Refer to: https://docs.djangoproject.com/en/1.8/ref/models/conditional-expressions/

This is a possibility:

from django.db.models.aggregates import Count
from django.db.models.expressions import F, Value, Case, When

projects = Project.objects.annotate(
        total_tasks=Count('task__pk'),
        completed_tasks=Count(Case(
           When(status=3, then=F('task__pk')),
           output_field=IntegerField()
        ))).all()