[Answered ]-Django orm multiple filter on same many to many field

by

1👍

✅

You should filter with:

datas = ['test', tester']
qs = Book.objects.all()
for datum in datas:
    qs = qs.filter(authors__first_name=datum)

But this will not scale well, and furthermore you will retrieve the same book.

What works better is to Count the number of Authors with test or tester as first name:

datas = set(['test', 'tester'])

Book.objects.filter(
    authors__first_name__in=datas
).annotate(
    nauthors=Count('authors')
).filter(nauthors=len(data))

or since , we can make use of the .alias(…) [Django-doc] to prevent counting twice (once for the filter, and once for the SELECT clause):

datas = set(['test', 'tester'])

Book.objects.filter(
    authors__first_name__in=datas
).alias(
    nauthors=Count('authors')
).filter(nauthors=len(data))

Here we thus will make one JOIN with the table of the Author model, and simply count the number of authors that match.

👤willeM_ Van Onsem

Leave a comment