django-orm对相同的多对多字段进行多重筛选

Question

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)

我想在下面的列表中找到包含first_name和last_name的作者的书籍，而不使用for循环(下面提到(或原始查询。

authors = [{
'first_name' : 'Test',
'last_name' : 'user1',
},
{'first_name' : 'Tester',
'last_name': 'user2'
}
]
queryset = Book,objects.all()
for i in authors:
queryset = queryset.filter(authors__first_name=i.get('first_name',None), authors__last_name=i.get('last_name',None))

Answer 1

我建议使用Q()以使其看起来更好：

from django.db.models import Q
query = Q()
for i in authors:
query |= Q(
authors__first_name=i['first_name'], 
authors__last_name=i['last_name']
)
queryset = Book.objects.filter(query)

Answer 2

在SQL中，这可能看起来像：

-- Note the DISTINCT since there could be multiple authors to
-- the same book and you probably only want to return it once.
SELECT DISTINCT
b.id,
b.title  
FROM
book AS b
JOIN book_author AS ba
ON b.id = ba.book_id
JOIN author AS a
ON ba.author_id = a.id
WHERE
-- You need a new line here for every pair of conditions...
(a.first_name = 'Test' AND a.last_name = 'user1')
OR (a.first_name = 'Tester' AND a.last_name = 'user2xx')

我可能错了，但我认为你不会为此使用for循环。

(使用上面的Q()得到了答案，看起来不错；无论如何，如果SQL对其他人有帮助，我们都会发布这个答案(