从不同类别获取最新对象的 Django 查询 [英] Django Query That Get Most Recent Objects From Different Categories

问题描述

我有两个模型 A 和 B.所有 B 对象都有一个 A 对象的外键.给定一组 A 对象，无论如何都可以使用 ORM 来获取一组 B 对象，其中包含为每个 A 创建的最新对象> 对象.

I have two models A and B. All B objects have a foreign key to an A object. Given a set of A objects, is there anyway to use the ORM to get a set of B objects containing the most recent object created for each A object.

这是一个简化的例子:

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()

因此，我正在寻找一个查询，该查询返回在美国 Anytown 的每家面包店中烘焙的最新蛋糕.

So I'm looking for a query that returns the most recent cake baked in each bakery in Anytown, USA.

推荐答案

据我所知，在 Django ORM 中没有一步到位的方法.

As far as I know, there is no one-step way of doing this in Django ORM.

但是您可以将其拆分为两个查询:

But you can split it in two queries:

from django.db.models import Max

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]
)

如果蛋糕的 id 与 bake_at 时间戳一起进行，您可以简化和消除上述代码的歧义(如果两个蛋糕同时到达，您可以同时获得它们):

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):

from django.db.models import Max

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)

顺便说一句，这要归功于 Daniel Roseman，他曾经回答过我的类似问题:

BTW credits for this goes to Daniel Roseman, who once answered similar question of mine:

http://groups.google.pl/group/django-users/browse_thread/thread/3b3cd4cbad478d34/3e4c87f336696054?hl=pl&q=

如果上面的方法太慢，那么我也知道第二种方法——你可以编写自定义SQL，只生成相关面包店中最热门的那些蛋糕，将其定义为数据库视图，然后为其编写非托管的Django模型.在上面的 django-users 线程中也提到了它.原始概念的直接链接在这里:

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:

http://web.archive.org/web/20130203180037/http://wolfram.kriesing.de/blog/index.php/2007/django-nice-and-critical-article#comment-48425

希望这会有所帮助.

这篇关于从不同类别获取最新对象的 Django 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！