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

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

Answer 2

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.