For the sake of simplicity let’s say I only have 2 models: Book, Author
class Author(models.Model): name = models.CharField(max_length='100') ... class Book(models.Model): name = models.CharField(max_length='100') authors = models.ManyToManyField(Author) ...
I want to filter Books using a list of authors. What I tried to do is:
authors = [...] # a list of author objects Books.objects.filter(authors__in=authors)
But here, the authors will be ORed when I want them ANDed.
Is there any way to AND many-to-many filtering??
You could & together a bunch of Q objects:
q = Q() for author in authors: q &= Q(authors=author) Books.objects.filter(q)
To exclude books that have authors outside the list, you could limit the query to books with exactly the number of authors in the list:
Based on comments, I think the requirement is to get all books authored by at least one author in the list, but exclude books with any authors outside the list.
So we build a queryset selecting the authors that we hate:
# this queryset will be embedded as a subquery in the next bad_authors = Author.objects.exclude(name__in=['A1', 'A2'])
Then exclude them to find the books we want:
# get all books without any of the bad_authors Books.objects.exclude(authors__in=bad_authors)
This will return all books except those authored by someone outside your list. If you also want to exclude those with no authors listed, add another exclude call:
This will leave us with just the books authored by one or more of the good ones!