Django Many To Many intersection filtering

django, django-queryset, orm, python

Solution

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!

Problem

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

Original source