Django query objects with all related objects meeting a condition

Question

Given these Django models:

class Job(models.Model):
    pass

class Task(models.Model):
    job = models.ForeignKey('Job', related_name='tasks')
    status = models.CharField(choices=TaskStatus.CHOICES, max_length=30)
    dependencies = models.ManyToManyField("self", related_name="dependents", symmetrical=False)

I want to query all Tasks with status PENDING and ALL dependencies having status COMPLETED for a single Job.

I wrote the following query, but it returns tasks that have at least one dependency with status completed, which is obviously not want I'm after.

tasks_that_can_be_executed = Task.objects.filter(
    job__pk=job_id,
    status=TaskStatus.PENDING,
    dependencies__status=TaskStatus.COMPLETED
)

Any suggestions?

Answer 1

from django.db.models import IntegerField, Case, When, Count, Q

Task.objects.filter(
    job=job,
    status=TaskStatus.PENDING,
).annotate(
    not_completed_dependencies_count=Count(
        Case(When(~Q(dependencies__status=TaskStatus.COMPLETED), then=1),
             output_field=IntegerField(),
        )
    )
).filter(
    not_completed_dependencies_count__gt=0
)