Django filter parent where ALL child values meet criteria

user_13667749 0
Viewed 1139

Based on these models:

class Job(models.Model):
    status = models.CharField(max_length=30)

class Task(models.Model):
    job = models.ForeignKey('Job', related_name='tasks')
    status = models.CharField(max_length=30)

I need a query that returns each Job where Job.status is null and ALL child Task.status's are "COMPLETE".

For context, when all Task.status's are complete, a comparison between values in each sibling Task will occur, it should not be carried out until all siblings Task is set to "COMPLETE", so the query will return those that are complete.

django-queryset django
1 Answers

We can work with an Exists subquery:

from django.db.models import Exists, OuterRef, Q

Job.objects.filter(
    ~Exists(
        Task.objects.filter(~Q(status='COMPLETE'), job_id=OuterRef('pk'))
    ),
    status=None
)

This will result in a query that looks like:

SELECT app_name_job.*
FROM app_name_job
WHERE NOT EXISTS (
  SELECT U0.id, U0.job_id, U0.status
  FROM app_name_task U0
  WHERE NOT U0.status = COMPLETE
    AND U0.job_id = app_name_job.id
  )
  AND app_name_job.status IS NULL)

prior to django-3.0, you will first need to annotate, and then filter:

from django.db.models import Exists, OuterRef, Q

Job.objects.annotate(
    all_complete=~Exists(
        Task.objects.filter(~Q(status='COMPLETE'), job_id=OuterRef('pk'))
    )
).filter(all_complete=True, status=None)

If a Job does not have any tasks, then all the tasks (well there are none) are considered complete, so such Jobs are also listed in the result if the status is None.

user_675790
Related