Conditional annotations in Django

Question

I got a simple requirement (not a simple implementation) and figured out how to achieve it without making multiple db calls and without .extra() in the query set.

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

Projects contain tasks that have different statuses. (Assume status = 3 completed) Now I want to list all the projects with their full tasks and completed tasks, for example below

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

I can get total_tasks with annotation but not completed tasks since the condition is required in the annotation. Is there any way to do this?

Answer 1

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

but did not use this example. Instead, I looked at the django code for aggregates and extended the Sum and Count objects myself.

Answer 2

This feature is new in Django 1.8.

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

This is an opportunity:

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

Answer 3

If you do not mind additional requests, you can get two sets of requests instead of one. The first can give you the total, and the second can filter on tasks_status and thereby get the completed quantity.

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