Conditional annotations in Django

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开发者_StackOverflow社区 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?

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

I don't know if it will help, but you can write your own custom annotation objects. I've just done it though without the conditional part. I based my solution on this link: http://www.voteruniverse.com/Members/jlantz/blog/conditional-aggregates-in-django

but didn't use the example there. Instead I looked at the django code for aggregates and extended the Sum and Count objects themselves.

If you do not mind additional queries, you can derive two querysets instead of one. The first can get you the total count and the second can filter on tasks_status and thereby get you the completed count.

from django.db.models import Count
all_projects = Project.objects.all()
total_counts = all_projects.annotate(count = Count('tasks'))
completed_counts = all_projects.filter(tasks_status = 3).annotate(count = Count('tasks'))