具有多对多字段的django过滤器模型 [英] django filter model with many-to-many fields

问题描述

我的模特:

class Book(models.Model):
    title = models.CharField(max_length=254)
    subtitle = models.CharField(max_length=254, null=True, blank=True)
    subjects = models.ManyToManyField(Subject)

class Subject(models.Model):
    name = models.CharField(max_length=255)
    description = models.CharField(max_length=254, null=True, blank=True)

此处的图书主题与主题"模型具有多对多的关系.

Here Book subjects is in many-to-many relationship with Subject model.

我如何才能获得所有具有相同相关主题的书.例如所有主题ID为[2,3,6]

How can i get all the books having the same related subjects. For example all the books having the subjects id [2,3,6]

推荐答案

带有 Q 对象的解决方案将无法工作.

The solution with the Q objects wont work.

一些初始数据:

>>> from subjects.models import Subject, Book
>>> s1 = Subject.objects.create(name='subject_1', description='description 1')
>>> s2 = Subject.objects.create(name='subject_2', description='description 2')
>>> s3 = Subject.objects.create(name='subject_3', description='description 3')
>>> s4 = Subject.objects.create(name='subject_4', description='description 4')
>>> b1 = Book.objects.create(title='one_subject_book', subtitle='one subject')
>>> b1.subjects = [s1]
>>> b2 = Book.objects.create(title='three_subject_book', subtitle='three subjects')
>>> b2.subjects = [s2,s3,s4]
>>> b3 = Book.objects.create(title='four_subject_book', subtitle='four subjects')
>>> b3.subjects = [s1,s2,s3,s4]

首先让我们检查 subjects__in = [s1，s2，s3] 的幼稚方法.由于仅 b3 包含所有主题，因此我们只搜索一本书.

Lets first check the naive approach with subjects__in=[s1,s2,s3]. We are looking for only one book as a result since only b3 contains all subjects.

>>> Book.objects.filter(subjects__in=[s1,s2,s3])
DEBUG (0.001) 
    SELECT `subjects_book`.`id`, `subjects_book`.`title`, `subjects_book`.`subtitle` 
    FROM `subjects_book` 
    INNER JOIN `subjects_book_subjects` ON ( `subjects_book`.`id` = `subjects_book_subjects`.`book_id` ) 
    WHERE `subjects_book_subjects`.`subject_id` IN (1, 2, 3) 
    LIMIT 21; args=(1, 2, 3)
[<Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>, <Book: Book object>]

我们只是在搜索包含 s1 或 s2 或 s3 的 books .这就是为什么我们得到这样的结果.我们有重复的结果，因为我们没有使用 .distinct()

We are simply searching for books containing s1 OR s2 OR s3. That's why we get such a result. We have duplicate results because we didn't used .distinct()

现在让我们尝试使用 Q 对象.

Now lets try with the Q objects.

只想提一下:

Book.objects.filter(Q(subjects=s1)&Q(subjects=s2)&Q(subjects=s3))

与以下相同:

Book.objects.filter(reduce(lambda q1,q2: q1&q2, [Q(subjects=s) for s in [s1,s2,s3]]))

更具动态性的版本，您可以轻松地通过主题更改列表.

Just a more dynamic version where u can easily just change the list with the subjects.

>>> Book.objects.filter(reduce(lambda q1,q2: q1&q2, [Q(subjects=s) for s in [s1,s2,s3]]))
DEBUG (0.260) 
    SELECT `subjects_book`.`id`, `subjects_book`.`title`, `subjects_book`.`subtitle` 
    FROM `subjects_book` 
    INNER JOIN `subjects_book_subjects` ON ( `subjects_book`.`id` = `subjects_book_subjects`.`book_id` ) 
    WHERE (
        `subjects_book_subjects`.`subject_id` = 1 
        AND `subjects_book_subjects`.`subject_id` = 2 
        AND `subjects_book_subjects`.`subject_id` = 3
    ) LIMIT 21; args=(1, 2, 3)
[]

我们得到了空的结果集.如果您检查查询，这将是正常的.我们不能同时包含 subject_id 的三个不同值的行.之所以得到这样的查询，是因为我们在单个 .filter 语句中一次应用了所有 Q 过滤器.在跨越多值关系中了解更多.

We got and empty result set. This would be normal if you inspect the query. We can't have a row containing three different values of subject_id at the same time. The reason we get such a query is because we apply all the Qfilters at once in a singe .filter statement. Read more about that at Spanning multi-valued relationships.

为了从 MySQL 的角度获得正确的结果，我们必须为每个想要的 subject 加入 subjects_book_subjects 表过滤.在 ORM 透视图中，这可以通过一系列连续的 .filter 语句来实现:

In order to achieve the correct result from MySQL point of view, we have to JOIN the subjects_book_subjects table for every subject we want to filter on. In the ORM perspective this can be achieved by series of consecutive .filter statements:

Book.objects.filter(subjects=s1).filter(subjects=s2).filter(subjects=s3)

以一种更时尚的方式看起来像这样:

In a more fashion way this look this:

books_queryset = reduce(lambda books,s: books.filter(subjects=s),[s1,s2,s3], Book.objects.all())

例如:

>>> reduce(lambda books,s: books.filter(subjects=s),[s1,s2,s3], Book.objects.all())
DEBUG (0.001) 
    SELECT `subjects_book`.`id`, `subjects_book`.`title`, `subjects_book`.`subtitle` 
    FROM `subjects_book` 
    INNER JOIN `subjects_book_subjects` ON ( `subjects_book`.`id` = `subjects_book_subjects`.`book_id` ) 
    INNER JOIN `subjects_book_subjects` T4 ON ( `subjects_book`.`id` = T4.`book_id` ) 
    INNER JOIN `subjects_book_subjects` T6 ON ( `subjects_book`.`id` = T6.`book_id` ) 
    WHERE (
        `subjects_book_subjects`.`subject_id` = 1 
        AND T4.`subject_id` = 2 
        AND T6.`subject_id` = 3
    ) LIMIT 21; args=(1, 2, 3)
[<Book: Book object>]

这篇关于具有多对多字段的django过滤器模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！