python Django 模型 - 如何过滤 ForeignKey 对象的数量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/258296/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Django models - how to filter number of ForeignKey objects
提问by kender
I have a models Aand B, that are like this:
我有一个模型A和B，是这样的：
class A(models.Model):
title = models.CharField(max_length=20)
(...)
class B(models.Model):
date = models.DateTimeField(auto_now_add=True)
(...)
a = models.ForeignKey(A)
Now I have some Aand Bobjects, and I'd like to get a query that selects all Aobjects that have less then 2 Bpointing at them.
现在我有一些A和B对象，我想得到一个查询，选择所有指向它们的A对象少于 2 B。
A is something like a pool thing, and users (the B) join pool. if there's only 1 or 0 joined, the pool shouldn't be displayed at all.
A 有点像池的东西，用户（B）加入池。如果只有 1 或 0 个加入，则根本不应该显示池。
Is it possible with such model design? Or should I modify that a bit?
这样的模型设计有可能吗？或者我应该稍微修改一下？
采纳答案by Jonny Buchanan
Sounds like a job for extra.
听起来像是一份工作extra。
A.objects.extra(
select={
'b_count': 'SELECT COUNT(*) FROM yourapp_b WHERE yourapp_b.a_id = yourapp_a.id',
},
where=['b_count < 2']
)
If the B count is something you often need as a filtering or ordering criterion, or needs to be displayed on list views, you could consider denormalisation by adding a b_count field to your A model and using signals to update it when a B is added or deleted:
如果 B 计数是您经常需要作为过滤或排序标准的东西，或者需要在列表视图中显示，您可以考虑通过向 A 模型添加 b_count 字段并在添加 B 时使用信号更新它来考虑非规范化或删除：
from django.db import connection, transaction
from django.db.models.signals import post_delete, post_save
def update_b_count(instance, **kwargs):
"""
Updates the B count for the A related to the given B.
"""
if not kwargs.get('created', True) or kwargs.get('raw', False):
return
cursor = connection.cursor()
cursor.execute(
'UPDATE yourapp_a SET b_count = ('
'SELECT COUNT(*) FROM yourapp_b '
'WHERE yourapp_b.a_id = yourapp_a.id'
') '
'WHERE id = %s', [instance.a_id])
transaction.commit_unless_managed()
post_save.connect(update_b_count, sender=B)
post_delete.connect(update_b_count, sender=B)
Another solution would be to manage a status flag on the A object when you're adding or removing a related B.
另一种解决方案是在添加或删除相关 B 时管理 A 对象上的状态标志。
B.objects.create(a=some_a)
if some_a.hidden and some_a.b_set.count() > 1:
A.objects.filter(id=some_a.id).update(hidden=False)
...
some_a = b.a
some_b.delete()
if not some_a.hidden and some_a.b_set.count() < 2:
A.objects.filter(id=some_a.id).update(hidden=True)
回答by gravitron
The question and selected answer are from 2008 and since then this functionality has been integrated into the django framework. Since this is a top google hit for "django filter foreign key count" I'd like to add an easier solution with a recent django version using Aggregation.
问题和选定的答案来自 2008 年，此后此功能已集成到 django 框架中。由于这是“django 过滤器外键计数”的热门谷歌热门搜索，我想使用Aggregation为最近的 django 版本添加一个更简单的解决方案。
from django.db.models import Count
cats = A.objects.annotate(num_b=Count('b')).filter(num_b__lt=2)
In my case I had to take this concept a step further. My "B" object had a boolean field called is_available, and I only wanted to return A objects who had more than 0 B objects with is_available set to True.
就我而言，我不得不将这个概念更进一步。我的“B”对象有一个名为 is_available 的布尔字段，我只想返回具有 0 个以上 B 对象且 is_available 设置为 True 的 A 对象。
A.objects.filter(B__is_available=True).annotate(num_b=Count('b')).filter(num_b__gt=0).order_by('-num_items')
回答by S.Lott
I'd recommend modifying your design to include some status field on A.
我建议修改您的设计以在 A 上包含一些状态字段。
The issue is one of "why?" Why does A have < 2 B's and why does A have >= 2 B's. Is it because user's didn't enter something? Or is because they tried and their input had errors. Or is it because the < 2 rule doesn't apply in this case.
问题是“为什么？”之一。为什么 A 有 < 2 个 B，为什么 A 有 >= 2 个 B。是因为用户没有输入内容吗？或者是因为他们尝试过并且他们的输入有错误。或者是因为 < 2 规则在这种情况下不适用。
Using presence or absence of a Foreign Key limits the meaning to -- well -- present or absent. You don't have any way to represent "why?"
使用外键的存在或不存在将含义限制为 - 好吧 - 存在或不存在。你没有办法表达“为什么？”
Also, you have the following option
此外，您还有以下选择
[ a for a in A.objects.all() if a.b_set.count() < 2 ]
This can be pricey because it does fetch all the A's rather than force the database to do the work.
这可能很昂贵，因为它确实获取了所有的 A，而不是强制数据库完成这项工作。
Edit: From the comment "would require me to watch for user join / user leaving the pool events".
编辑：从评论“需要我注意用户加入/用户离开池事件”。
You don't "watch" anything -- you provide an API which does what you need. That's the central benefit of the Django model. Here's one way, with explict methods in the Aclass.
你不“看”任何东西——你提供了一个满足你需要的 API。这是 Django 模型的核心优势。这是一种方法，在A类中使用显式方法。
class A( models.Model ):
....
def addB( self, b ):
self.b_set.add( b )
self.changeFlags()
def removeB( self, b ):
self.b_set.remove( b )
self.changeFlags()
def changeFlags( self ):
if self.b_set.count() < 2: self.show= NotYet
else: self.show= ShowNow
You can also define a special Managerfor this, and replace the default b_setManager with your manager that counts references and updates A.
您还可以Manager为此定义一个特殊的，并将默认的b_setManager替换为您对引用和更新计数的经理A。
回答by un33k
I assume that joining or leaving the pool may not happen as often as listing (showing) the pools. I also believe that it would be more efficient for the users join/leave actions to update the pool display status. This way, listing & showing the pools would require less time as you would just run a single query for SHOW_STATUS of the pool objects.
我认为加入或离开池可能不会像列出（显示）池那样频繁。我也相信用户加入/离开操作更新池显示状态会更有效。这样，列出和显示池将需要更少的时间，因为您只需对池对象的 SHOW_STATUS 运行单个查询。
