Django 聚合和查询
我有许多可以属于一种或多种类型的元素。
class Type(models.Model):
name = models.CharField(max_length=128, unique=True)
class Element(models.Model):
name = models.CharField(max_length=128, unique=True)
type = models.ManyToManyField('Type')
假设我有 3 种类型和 3 个元素:
In [3]: Type.objects.all()
Out[3]: [<Type: His Type>, <Type: My Type>, <Type: Your Type>]
In [4]: [(e,e.type.all()) for e in Element.objects.all()]
Out[4]:
[(<Element: First Element>, [<Type: My Type>]),
(<Element: Second Element>, [<Type: Your Type>]),
(<Element: Third Element>,
[<Type: My Type>, <Type: Your Type>, <Type: His Type>])]
我正在尝试获取一个查询集,其中包含仅属于“我的类型”类型的元素
我的想法是获取此类型的元素并检查它们是否仅属于一种类型。
但由于某种原因,它认为“第三元素”只有一种类型,
In [5]: my_type=Type.objects.get(name='My Type')
In [6]: my_type.element_set.annotate(num_types=Count('type')).filter(num_types__exact=1)
Out[6]: [<Element: First Element>, <Element: Third Element>]
In [7]: [(e,e.num_types) for e in my_type.element_set.annotate(num_types=Count('type'))]
Out[7]: [(<Element: First Element>, 1), (<Element: Third Element>, 1)]
而它是三种类型,
In [8]: Element.objects.get(name='Third Element').type.count()
Out[8]: 3
我做错了什么?
I have a number of elements that can be of one or more types.
class Type(models.Model):
name = models.CharField(max_length=128, unique=True)
class Element(models.Model):
name = models.CharField(max_length=128, unique=True)
type = models.ManyToManyField('Type')
Let's say that I have 3 types and 3 elements:
In [3]: Type.objects.all()
Out[3]: [<Type: His Type>, <Type: My Type>, <Type: Your Type>]
In [4]: [(e,e.type.all()) for e in Element.objects.all()]
Out[4]:
[(<Element: First Element>, [<Type: My Type>]),
(<Element: Second Element>, [<Type: Your Type>]),
(<Element: Third Element>,
[<Type: My Type>, <Type: Your Type>, <Type: His Type>])]
I'm trying to get a queryset with the elements that are only of the type "My Type"
My idea was to get the elements of this type and check that they are only of one type.
But for some reason it thinks that "Third Element" is of only one type
In [5]: my_type=Type.objects.get(name='My Type')
In [6]: my_type.element_set.annotate(num_types=Count('type')).filter(num_types__exact=1)
Out[6]: [<Element: First Element>, <Element: Third Element>]
In [7]: [(e,e.num_types) for e in my_type.element_set.annotate(num_types=Count('type'))]
Out[7]: [(<Element: First Element>, 1), (<Element: Third Element>, 1)]
when it is of three types
In [8]: Element.objects.get(name='Third Element').type.count()
Out[8]: 3
What I'm doing wrong?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
鉴于这是标准的多对多,您可以调用“my_type.element_set.all()”,它应该为您提供该类型的 Element 实例。您还可以采用另一种方式(使用模型名称的复数版本,而不是“_set”)来获取元素类型,如下所示:“my_element.types.all()”。
要获取计数,只需执行“my_type.element_set.count()”即可。
Given that this is a standard many-to-many, you can call 'my_type.element_set.all()', that should give you the Element instances that are of that type. You can also go the other way (using pluralized version of model name, not '_set') to get an elements types like so: 'my_element.types.all()'.
To get the count, just do 'my_type.element_set.count()'.
Django 在后台执行的 SQL 查询将您限制为仅包含
type_element_type.element_id = 1
的行。因此,带注释的计数仅适用于这些,因此每个 1。上面的查询 [7] 变为此 SQL(如 这个):
以及来自 sqlite3:
The SQL query that Django is performing behind the scenes is limiting you to only the rows that contain
type_element_type.element_id = 1
. So the annotated count is only applying to those, hence 1 each.Your query [7] above becomes this SQL (as shown by this):
and from sqlite3:
我结束了
Element.objects.filter(pk__in=my_type.element_set.values_list('pk', flat=True)).annotate(num_types=Count('type')).filter(num_types=1)
这样,我就不会受到从
my_type.element_set
开始时发生的连接的影响。如果您想知道所涉及的 SQL:
在
my_type.element_set.annotate(num_types=Count) 中('type')).filter(num_types__exact=1)
它的作用是:在 Element.objects.filter(pk__in=my_type.element_set.values_list('pk', flat=True)).annotate 中(num_types=Count('type')).filter(num_types=1) 它的作用是:
I ended doing
Element.objects.filter(pk__in=my_type.element_set.values_list('pk', flat=True)).annotate(num_types=Count('type')).filter(num_types=1)
That way I'm not affected by the join that happens when I start with
my_type.element_set
If you are wondering about the SQL involved:
In
my_type.element_set.annotate(num_types=Count('type')).filter(num_types__exact=1)
it does:While in
Element.objects.filter(pk__in=my_type.element_set.values_list('pk', flat=True)).annotate(num_types=Count('type')).filter(num_types=1)
it does: