Django过滤器queryset __in用于*列表中的每个*项目 [英] Django filter queryset __in for *every* item in list
问题描述
class Photo(models.Model):
tags = models。 ManyToManyField(Tag)
class标签(models.Model):
name = models.CharField(max_length = 50)
在一个视图中,我列出了一个名为类别的有效过滤器。
我想过滤在类别中存在所有标签的照片对象。
我试过:
Photo.objects.filter(tags__name__in =类别)
但是,这些匹配任何项目在类别,而不是所有项目。
所以如果类别是['假期','夏天']我想要照片的假日和夏天标签。 >
可以实现吗?
摘要:
根据评论中的jpic和sgallen的建议,一个选项是添加 .filter()
每个类别。每个额外的过滤器
添加更多的联接,这对于小的类别不应该是一个问题。
聚合 方法。您可以选择使用 com / en / dev / topics / db / sql /rel =noreferrer>自定义查询
一些例子
测试设置:
class Photo(models.Model):
tags = models.ManyToManyField('Tag')
class标签(models.Model):
name = models.CharField (max_length = 50)
def __unicode __(self):
return self.name
在[2]中:t1 = Tag.objects.create(name = 'holiday')
在[3]中:t2 = Tag.objects.create(name ='summer')
在[4]中:p = Photo.objects.create()
In [5]:p.tags.add(t1)
在[6]中:p.tags.add(t2)
在[7]中:p.tags.all()
出[7]:[<标签:假日><标签:夏天>]
a href =https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-mult i-valued-relationshiprel =noreferrer>链式过滤器方法:
在[8]中:照片.objects.filter(tags = t1).filter(tags = t2)
输出[8]:[<照片:照片对象>]
结果查询:
在[17]中:打印Photo.objects。 filter(tags = t1).filter(tags = t2).query
SELECTtest_photo。id
FROMtest_photo
INNER JOINtest_photo_tagsON(test_photo id=test_photo_tags。photo_id)
INNER JOINtest_photo_tagsT4 ON(test_photoid= T4。photo_id)
WHERE(test_photo_tags = 3 AND T4。tag_id= 4)
请注意,每个过滤器
添加更多 JOINS
到查询。
使用注释 方法:
[29]:from django.db.models import Count
在[30]中:Photo.objects.filter(tags__in = [t1,t2])。annotate(num_tags = Count('tags'))。filter num_tags = 2)
输出[30]:[<照片:照片对象>]
结果查询:
在[32]中:打印Photo.objects.filter(tags__in = [t1,t2])。 (num_tags = Count('tags'))。filter(num_tags = 2).query
SELECTtest_photoid,COUNT(test_photo_tagstag_id)ASnum_tags
FROMtest_photo
LEFT OUTER JOINtest_photo_tagsON(test_photo。id=test_photo_tags。photo_id)
WHERE(test_photo_tags。tag_idIN )
GROUP BYtest_photo。id,test_photo。id
HAVING COUNT(test_photo_tags。tag_id)= 2
AND
ed Q
不工作:
在[9]中:从django.db.models导入Q
在[10]中:照片.objects.filter(Q(tags__name ='holiday')& Q(tag__name ='summer'))
输出[10]:[]
在[11]中:从运算符import和_
在[12]中:Photo.objects.filter(reduce and_,[Q(tags__name ='holiday'),Q(tags__name ='summer')]))
Out [12]:[]
结果查询:
在[25]中:打印Photo.objects。过滤器(Q(tags__name ='holiday')& Q(tags__name ='summer'))查询
SELECTtest_photoid
FROMtest_photo
INNER JOIN test_photo_tagsON(test_photo。id=test_photo_tags。photo_id)
INNER JOINtest_tagON(test_photo_tagstag_id=test_tagid)
WHERE(test_tag。name= holiday ANDtest_tag。name= summer)
Let's say I have the following models
class Photo(models.Model):
tags = models.ManyToManyField(Tag)
class Tag(models.Model):
name = models.CharField(max_length=50)
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.
I tried:
Photo.objects.filter(tags__name__in=categories)
But this matches any item in categories, not all items.
So if categories would be ['holiday', 'summer'] I want Photo's with both a holiday and summer tag.
Can this be achieved?
Summary:
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.
You also have the option of using custom queries.
Some examples
Test setup:
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>]
Using chained filters approach:
In [8]: Photo.objects.filter(tags=t1).filter(tags=t2)
Out[8]: [<Photo: Photo object>]
Resulting query:
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 )
Note that each filter
adds more JOINS
to the query.
Using annotation approach:
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>]
Resulting query:
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
AND
ed 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]: []
Resulting query:
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过滤器queryset __in用于*列表中的每个*项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!