Django 过滤器查询集 __in 用于列表中的*每个*项目 [英] Django filter queryset __in for *every* item in list

问题描述

假设我有以下模型

class Photo(models.Model):
    tags = models.ManyToManyField(Tag)

class Tag(models.Model):
    name = models.CharField(max_length=50)

在一个视图中，我有一个包含活动过滤器的列表，称为categories.我想过滤 categories 中存在所有标签的 Photo 对象.

In a view I have a list with active filters called categories. I want to filter Photo objects which have all tags present in categories.

我试过了:

Photo.objects.filter(tags__name__in=categories)

但是这匹配类别中的任何项目，而不是所有项目.

But this matches any item in categories, not all items.

因此，如果类别是 ['holiday', 'summer'] 我想要带有假日和夏季标签的照片.

So if categories would be ['holiday', 'summer'] I want Photo's with both a holiday and summer tag.

这能实现吗?

推荐答案

总结:

一个选项是，正如 jpic 和 sgallen 在评论中所建议的，为每个类别添加 .filter().每个额外的 filter 都会增加更多的连接，这对于小类别的集合来说应该不是问题.

One option is, as suggested by jpic and sgallen in the comments, to add .filter() for each category. Each additional filter adds more joins, which should not be a problem for small set of categories.

有聚合 方法.对于大量类别，此查询会更短，甚至可能更快.

There is the aggregation approach. This query would be shorter and perhaps quicker for a large set of categories.

您还可以选择使用自定义查询.

一些例子

测试设置:

class Photo(models.Model):
    tags = models.ManyToManyField('Tag')

class Tag(models.Model):
    name = models.CharField(max_length=50)

    def __unicode__(self):
        return self.name

In [2]: t1 = Tag.objects.create(name='holiday')
In [3]: t2 = Tag.objects.create(name='summer')
In [4]: p = Photo.objects.create()
In [5]: p.tags.add(t1)
In [6]: p.tags.add(t2)
In [7]: p.tags.all()
Out[7]: [<Tag: holiday>, <Tag: summer>]

使用链式过滤器方法:

In [8]: Photo.objects.filter(tags=t1).filter(tags=t2)
Out[8]: [<Photo: Photo object>]

结果查询:

In [17]: print Photo.objects.filter(tags=t1).filter(tags=t2).query
SELECT "test_photo"."id"
FROM "test_photo"
INNER JOIN "test_photo_tags" ON ("test_photo"."id" = "test_photo_tags"."photo_id")
INNER JOIN "test_photo_tags" T4 ON ("test_photo"."id" = T4."photo_id")
WHERE ("test_photo_tags"."tag_id" = 3  AND T4."tag_id" = 4 )

请注意，每个 filter 都会向查询添加更多的 JOINS.

Note that each filter adds more JOINS to the query.

In [29]: from django.db.models import Count
In [30]: Photo.objects.filter(tags__in=[t1, t2]).annotate(num_tags=Count('tags')).filter(num_tags=2)
Out[30]: [<Photo: Photo object>]

结果查询:

In [32]: print Photo.objects.filter(tags__in=[t1, t2]).annotate(num_tags=Count('tags')).filter(num_tags=2).query
SELECT "test_photo"."id", COUNT("test_photo_tags"."tag_id") AS "num_tags"
FROM "test_photo"
LEFT OUTER JOIN "test_photo_tags" ON ("test_photo"."id" = "test_photo_tags"."photo_id")
WHERE ("test_photo_tags"."tag_id" IN (3, 4))
GROUP BY "test_photo"."id", "test_photo"."id"
HAVING COUNT("test_photo_tags"."tag_id") = 2

ANDed Q 对象将不起作用:

ANDed Q objects would not work:

In [9]: from django.db.models import Q
In [10]: Photo.objects.filter(Q(tags__name='holiday') & Q(tags__name='summer'))
Out[10]: []
In [11]: from operator import and_
In [12]: Photo.objects.filter(reduce(and_, [Q(tags__name='holiday'), Q(tags__name='summer')]))
Out[12]: []

结果查询:

In [25]: print Photo.objects.filter(Q(tags__name='holiday') & Q(tags__name='summer')).query
SELECT "test_photo"."id"
FROM "test_photo"
INNER JOIN "test_photo_tags" ON ("test_photo"."id" = "test_photo_tags"."photo_id")
INNER JOIN "test_tag" ON ("test_photo_tags"."tag_id" = "test_tag"."id")
WHERE ("test_tag"."name" = holiday  AND "test_tag"."name" = summer )

这篇关于Django 过滤器查询集 __in 用于列表中的*每个*项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！