CODEWITHSUNDEEP
djangodjango-modelsdjango-orm
Madmax
Madmax

Reputation: 125

django orm multiple filter on same many to many field

class Book(models.Model):
    title = models.CharField(max_length=50)
    authors = models.ManyToManyField(Author)

class Author(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=40)

Suppose I want to get books having atleast two author's whose first names are Test and Tester.So I will proceed with Book.objects.filter(authors__first_name='Test').filter(authors__first_name='Tester')

What if I have multiple first name(long list) to check,apart from running a for loop and rawsqlquery are there other options?

queryset = Book,objects.all()
for i in ['test','tester']:
   queryset = queryset.filter(authors__first_name=i)

Upvotes: 1

Views: 120

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476534

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.

Upvotes: 1

Related Questions