CODEWITHSUNDEEP
djangoannotate
Narendra Kamma
Narendra Kamma

Reputation: 1441

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 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?

Upvotes: 6

Views: 5444

Answers (3)

Jônatas Castro
Jônatas Castro

Reputation: 493

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

Upvotes: 10

Harel
Harel

Reputation: 2039

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.

Upvotes: 0

Manoj Govindan
Manoj Govindan

Reputation: 74705

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

Upvotes: 0

Related Questions