Django ORM：通过max（datetime字段，max（相关项目的datetime字段））使用额外的模型 [英] Django ORM: using extra to order models by max(datetime field, max(datetime field of related items))

问题描述

给定以下模型：

class BaseModel(models.Model):
    modified_date = models.DateTimeField(auto_now=True)
    class Meta:
        abstract = True

class Map(BaseModel):
    ...

class MapItem(BaseModel):
    map = models.ForeignKey(Map)
    ...

如何构建我的ORM调用以排序 Maps 最后一次 Map 或其中一个 MapItems 已修改？

How do I structure my ORM call to sort Maps by the last time either the Map or one of its MapItems was modified?

换句话说，我如何为每个 Map 生成一个代表$ $ c的最大值的值$ c> Map的自己的 modified_date 和最近的 modified_date 相关的 MapItems ，并且不使用原始SQL或Python进行排序？

In other words, how do I generate a value for each Map that represents the maximum of the Map's own modified_date and the latest modified_date of its related MapItems and sort by it without resorting to raw SQL or Python?

我尝试了以下查询，但$ 当我的QuerySet被评估时，last_updated 值为空，我不太确定为什么：

I tried the following query but the last_updated values are blank when my QuerySet is evaluated and I'm not quite sure why:

Map.objects.extra(select={
    "last_updated": "select greatest(max(maps_mapitem.modified_date), maps_map.modified_date) 
     from maps_map join maps_mapitem on maps_map.id = maps_mapitem.id"}).

提前感谢

编辑0：， Peter DeGlopper指出，我的加入不正确。我修正了加入，而$ code> last_updated 的值现在都是相等的，而不是空白：

Edit 0: as Peter DeGlopper points out, my join was incorrect. I've fixed the join and the last_updated values are now all equal instead of being blank:

Map.objects.extra(select={
    "last_updated": "select greatest(max(maps_mapitem.modified_date), maps_map.modified_date) 
     from maps_map join maps_mapitem on maps_map.id = maps_mapitem.maps_id"}).

推荐答案

您的加入错误。它应该是：

Your join is wrong. It should be:

maps_map join maps_mapitem on maps_map.id = maps_mapitem.map_id

由于这样，您迫使PK平等，而不是地图的PK匹配项目的FK。

As it stands you're forcing the PKs to be equal, not the map's PK to match the items' FKs.

编辑

我怀疑你的子查询没有加入查询的main map_map部分。我确定还有其他方法可以做到这一点，但这应该是有效的：

I suspect your subquery isn't joining against the main maps_map part of the query. I am sure there are other ways to do this, but this should work:

Map.objects.extra(select={
  "last_updated": "greatest(modified_date, (select max(maps_mapitem.modified_date) from maps_mapitem where maps_mapitem.map_id = maps_map.id))"})

这篇关于Django ORM：通过max（datetime字段，max（相关项目的datetime字段））使用额外的模型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！