Ufdjrw

I have two models in a ManyToMany relationship. I want to get a subset of the first model for which every related model matches certain criteria.

Scenario: This is for a collaborative dictionary. A Word may have 0 or more Definitions. A Definition may be in a number of states: "complete", "in progress" or "abandoned". I am interested in finding all Words which have either no Definitions or only have "abandoned" Definitions.

class DefinitionState(Enum):
 COMPLETE = 0
 IN_PROGRESS = 1
 ABANDONED = 2

class Definition(models.Model):
 text = models.TextField()
 status = models.IntegerField()

class Word(models.Model):
 text = models.CharField(length=60)
 definitions = models.ManyToManyField(Definition, related="words")

spam = Word.objects.create(text="spam")
eggs = Word.objects.create(text="eggs")
bacon = Word.objects.create(text="bacon")
lobster = Word.objects.create(text="lobster")

spam_d1 = Definition.objects.create(
 text="Processed meat", 
 status=DefinitionState.COMPLETE
)
spam_d2 = Definition.objects.create(
 text="Unwanted email",
 status=DefinitionState.ABANDONED
)
spam.definitions.add(spam_d1)
spam.definitions.add(spam_d2)

eggs_d = Definition.objects.create(
 text="Laid by chickens",
 status=DefinitionState.COMPLETE
)
eggs.definitions.add(eggs_d)

bacon_d = Definition.objects.create(
 text="Part of a pig",
 status=DefinitionState.ABANDONED
)
bacon.definitions.add(bacon_d)

So I have four Words:

• 
  spam, which has one COMPLETE definition and one ABANDONED definition;


• 
  eggs, which has one COMPLETE definition;


• 
  bacon, which has one ABANDONED definition;


• 
  lobster, which has no definitions.

I want to write a query which will return (bacon, lobster) (in no particular order).

I can write the query to return lobster:

Word.objects.annotate(Count("definitions")).filter(definitions__count=0)

and I can identify which definitions are abandoned:

Definition.objects.filter(status=DefinitionState.ABANDONED)

but I can't figure out how to ask for Words whose Definitions are all ABANDONED.

Many thanks in advance

Words.objects.exclude(definitions__status__in=[COMPLETE, IN_PROGRESS])