python 从不同类别获取最新对象的 Django 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2074514/
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 Query That Get Most Recent Objects From Different Categories
提问by Zach
I have two models Aand B. All Bobjects have a foreign key to an Aobject. Given a set of Aobjects, is there anyway to use the ORM to get a set of Bobjects containing the most recent object created for each Aobject.
我有两个模型A和B. 所有B对象都有一个对象的外键A。给定一组A对象，无论如何都可以使用 ORM 来获取一组B包含为每个A对象创建的最新对象的对象。
Here's an simplified example:
这是一个简化的示例：
class Bakery(models.Model):
town = models.CharField(max_length=255)
class Cake(models.Model):
bakery = models.ForeignKey(Bakery, on_delete=models.CASCADE)
baked_at = models.DateTimeField()
So I'm looking for a query that returns the most recent cake baked in each bakery in Anytown, USA.
因此，我正在寻找一个查询，该查询返回在美国 Anytown 的每个面包店中烘焙的最新蛋糕。
采纳答案by Tomasz Zieliński
As far as I know, there is no one-step way of doing this in Django ORM.
据我所知，在 Django ORM 中没有一步到位的方法。
But you can split it in two queries:
但是您可以将其拆分为两个查询：
bakeries = Bakery.objects.annotate(
hottest_cake_baked_at=Max('cake__baked_at')
)
hottest_cakes = Cake.objects.filter(
baked_at__in=[b.hottest_cake_baked_at for b in bakeries]
)
If id's of cakes are progressing along with bake_at timestamps, you can simplify and disambiguate the above code (in case two cakes arrives at the same time you can get both of them):
如果蛋糕的 id 与 bake_at 时间戳一起进行，您可以简化和消除上述代码的歧义（如果两个蛋糕同时到达，您可以同时获得它们）：
hottest_cake_ids = Bakery.objects.annotate(
hottest_cake_id=Max('cake__id')
).values_list('hottest_cak??e_id', flat=True)
hottest_cakes = Cake.objects.filter(id__in=hottest_cake_ids)
BTW credits for this goes to Daniel Roseman, who once answered similar question of mine:
顺便说一句，这要归功于 Daniel Roseman，他曾经回答过我的类似问题：
If the above method is too slow, then I know also second method - you can write custom SQL producing only those Cakes, that are hottest in relevant Bakeries, define it as database VIEW, and then write unmanaged Django model for it. It's also mentioned in the above django-users thread. Direct link to the original concept is here:
如果上述方法太慢，那么我也知道第二种方法 - 您可以编写仅生成相关 Bakeries 中最热门的 Cakes 的自定义 SQL，将其定义为数据库 VIEW，然后为其编写非托管 Django 模型。在上面的 django-users 线程中也提到了它。原始概念的直接链接在这里：
Hope this helps.
希望这可以帮助。
回答by Todor
Starting from Django 1.11and thanks to Subqueryand OuterRefand we can finally build a latest-per-groupquery using the ORM.
从开始Django 1.11并感谢子查询和OuterRef，我们终于可以建立一个latest-per-group使用查询ORM。
hottest_cakes = Cake.objects.filter(
baked_at=Subquery(
(Cake.objects
.filter(bakery=OuterRef('bakery'))
.values('bakery')
.annotate(last_bake=Max('baked_at'))
.values('last_bake')[:1]
)
)
)
#BONUS, we can now use this for prefetch_related()
bakeries = Bakery.objects.all().prefetch_related(
Prefetch('cake_set',
queryset=hottest_cakes,
to_attr='hottest_cakes'
)
)
#usage
for bakery in bakeries:
print 'Bakery %s has %s hottest_cakes' % (bakery, len(bakery.hottest_cakes))
回答by dbn
If you happen to be using PostGreSQL, you can use Django's interface to DISTINCT ON:
如果您碰巧使用 PostGreSQL，则可以使用Django 的 DISTINCT ON 接口：
recent_cakes = Cake.objects.order_by('bakery__id', '-baked_at').distinct('bakery__id')
As the docssay, you must order bythe same fields that you distinct on. As Simon pointed out below, if you want to do additional sorting, you'll have to do it in Python-space.
正如文档所说，您必须order by与您的字段相同distinct on。正如西蒙在下面指出的那样，如果您想进行额外的排序，则必须在 Python 空间中进行。
回答by Daniel Roseman
This should do the job:
这应该可以完成这项工作：
from django.db.models import Max
Bakery.objects.annotate(Max('cake__baked_at'))
回答by Ivan Klass
I was fighting with similar problem and finally come to following solution. It does not rely on order_byand distinctso can be sorted as desired on db-side and also can be used as nested query for filtering. I also believe this implementation is db engine independent, because it's based on standard sql HAVINGclause. The only drawback is that it will return multiple hottest cakes per bakery, if they are baked in that bakery at exactly same time.
我正在与类似的问题作斗争，最后得出以下解决方案。它不依赖order_by，distinct因此可以在 db 端根据需要进行排序，也可以用作嵌套查询进行过滤。我也相信这个实现是独立于数据库引擎的，因为它基于标准的 sqlHAVING子句。唯一的缺点是它会在每个面包店返回多个最热的蛋糕，如果它们是在同一时间在那个面包店烘焙的。
from django.db.models import Max, F
Cake.objects.annotate(
# annotate with MAX "baked_at" over all cakes in bakery
latest_baketime_in_bakery=Max('bakery__cake_set__baked_at')
# compare this cake "baked_at" with annotated latest in bakery
).filter(latest_baketime_in_bakery__eq=F('baked_at'))
回答by twknab
Cake.objects.filter(bakery__town="Anytown").order_by("-created_at")[:1]
I haven't built out the models on my end, but in theory this should work. Broken down:
我还没有最终建立模型，但理论上这应该可行。分解：
Cake.objects.filter(bakery__town="Anytown")Should return any cakes whom belong to "Anytown", assuming the country is not part of the string. The double underscores betweenbakeryandtownallow us to access thetownproperty ofbakery..order_by("-created_at")will order the results by their created date, most recent first (take note of the-(minus) sign in"-created_at". Without the minus sign, they'd be ordered by oldest to most recent.[:1]on the end will return only the 1st item in the list which is returned (which would be a list of cakes from Anytown, sorted by most recent first).
Cake.objects.filter(bakery__town="Anytown")应该返回属于“Anytown”的任何蛋糕，假设国家不是字符串的一部分。bakery和之间的双下划线town允许我们访问 的town属性bakery。.order_by("-created_at")将按创建日期对结果进行排序，最近的在前（注意-（减号）登录"-created_at"。如果没有减号，它们将按最旧到最近排序。[:1]最后将只返回返回列表中的第一个项目（这将是来自 Anytown 的蛋糕列表，按最近的排序）。
Note: This answer is for Django 1.11.This answer modified from Queries shown here in Django 1.11 Docs.
注意：此答案适用于 Django 1.11。这个答案修改自Django 1.11 Docs 中显示的查询。
