Django查询，可以从不同类别获取最新的对象。

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

我有两个模型A和B。所有B对象都有A对象的外键。给定一组对象，是否存在使用ORM获取一组包含为每个a对象创建的最新对象的B对象的集合

Here's an simplified example:

这是一个简化的例子:

Class Bakery(models.Model):
    town = models.CharField()

Class Cake(models.Model):
    bakery = models.ForeignKey(Bakery)
    baked_at = models.DateTimeField()

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

因此，我正在寻找一个查询，该查询返回在美国任何一个城镇的每个面包店中烤的最新蛋糕。

6 个解决方案

#1

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

如果我的蛋糕和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)也对我的问题负责:

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

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:

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

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

http://web.archive.org/web/20130203180037/http:/ / wolfram.kriesing.de /博客/ index . php / 2007 / django-nice-and-critical-article #注释- 48425

Hope this helps.

希望这个有帮助。

#2

If you happen to be using PostGreSQL, you can use Django's interface to DISTINCT ON:

如果您碰巧使用PostGreSQL，您可以使用Django的接口来对:

recent_cakes = Cake.objects.order_by('bakery__id', '-baked_at').distinct('bakery__id')

As the docs say, you must order by the 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.

正如文档所言，您必须按照您所区分的字段进行排序。正如Simon在下面指出的，如果你想做额外的排序，你必须在python空间里做。

#3

Starting from Django 1.11 and thanks to Subquery and OuterRef and we can finally build a latest-per-group query using the ORM.

从Django 1.11开始，多亏了Subquery和OuterRef，我们最终可以使用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))

#4

This should do the job:

这应该可以完成以下工作:

from django.db.models import Max
Bakery.objects.annotate(Max('cake__baked_at'))

#5

I was fighting with similar problem and finally come to following solution. It does not rely on order_by and distinct so 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 HAVING clause. 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和不同的查询，因此可以在dbside上按需要进行排序，也可以用作过滤的嵌套查询。我还认为这个实现是独立于db引擎的，因为它基于标准的sql have子句。唯一的缺点是，如果它们是在同一时间在面包店里烘焙的，它将会为每家烘焙店带来多个最热门的蛋糕。

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

#6

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 between bakery and town allow us to access the town property of bakery.
过滤器(bakery__town=“Anytown”)应该返回属于“Anytown”的蛋糕，假设这个国家不是字符串的一部分。面包店和town之间的双重下划线允许我们进入town property of bakery。
.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.
.order_by(“-created_at”)将根据创建的日期对结果排序，最近的日期是“-created_at”中的-(-)符号。如果没有减号，他们会被年龄最大的人订购。
[: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).
[:1]最后将只返回列表中返回的第一个项目(它将是来自任何一个城镇的蛋糕列表，按最近的第一个排序)。

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文档中显示的查询。

#1