Django Many To Many intersection filtering

Question

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??

Answer 1

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:

Books.objects.annotate(count=Count('authors')).filter(count=len(authors)).filter(q)

Update:

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:

Books.objects.exclude(authors__in=bad_authors).exclude(authors=None)

This will leave us with just the books authored by one or more of the good ones!