Django: ManyToMany filter matching on ALL items in a list

Solution 1:

Similar to @goliney's approach, I found a solution. However, I think the efficiency could be improved.

# A sample set of authors
target_authors = set((author_1, author_2))

# To reduce the search space, first retrieve those books with just 2 authors.
candidate_books = Book.objects.annotate(c=Count('authors')).filter(c=len(target_authors))

# In each iteration, we filter out those books which don't contain one of the 
# required authors - the instance on the iteration.
for author in target_authors:
    candidate_books = candidate_books.filter(authors=author)

final_books = candidate_books

Solution 2:

You can use complex lookups with Q objects

from django.db.models import Q
...
target_authors = set((author_1, author_2))
q = Q()
for author in target_authors:
    q &= Q(authors=author)
Books.objects.annotate(c=Count('authors')).filter(c=len(target_authors)).filter(q)

Solution 3:

Q() & Q() is not equal to .filter().filter(). Their raw SQLs are different where by using Q with &, its SQL just add a condition like WHERE "book"."author" = "author_1" and "book"."author" = "author_2". it should return empty result.

The only solution is just by chaining filter to form a SQL with inner join on same table: ... ON ("author"."id" = "author_book"."author_id") INNER JOIN "author_book" T4 ON ("author"."id" = T4."author_id") WHERE ("author_book"."author_id" = "author_1" AND T4."author_id" = "author_1")