In Django, how do I filter based on all entities in a many-to-many relation instead of any?

Question

I have a model like this:

class Task(models.model):
    TASK_STATUS_CHOICES = (
        (u"P", u'Pending'),
        (u"A", u'Assigned'),
        (u"C", u'Complete'),
        (u"F", u'Failed')
    )
    status = models.CharField(max_length=2, choices=TASK_STATUS_CHOICES)
    prerequisites = models.ManyToManyField('self', symmetrical=False, related_name="dependents")

I want to find all tasks whose prerequisites are all complete. I have tried:

Task.objects.filter(prerequisites__status=u"C")

This gets all tasks for which any prerequisite is complete. I thought maybe I need to use an annotation, but I can't see how to apply a filter on the prerequisite tasks before doing an aggregation. For example, I can find the number of prerequisites of each task like this:

Task.objects.annotate(prereq_count=Count('prerequisites'))

But how do I annotate tasks with the number of their prerequisites that have a status not equal to "C"?

Answer 1

For your first question -- 'all tasks whose prerequisites are all complete':

>>> Task.objects.exclude(prerequisites__status__in=['A','P','F'])

This will also include tasks with no prerequisites (as they have no incomplete prerequisites). As a doctest (using your model definition), the following passes:

>>> a = Task.objects.create(status='C')
>>> b = Task.objects.create(status='A')
>>> b.prerequisites.add(a)
>>> c = Task.objects.create(status='P')
>>> c.prerequisites.add(b)
>>> prerequisites_complete = Task.objects.exclude(prerequisites__status__in=['A','P','F'])
>>> set([t.id for t in prerequisites_complete]) == set([a.id, b.id])
True

This doesn't answer how many incomplete prerequisites each task has -- which you might need for display, optimization, etc.