Django：选择具有最大时间戳记的值或加入同一张表 [英] Django: select values with max timestamps or join to the same table

问题描述

我有一个简单的Django模型

I have a simple Django models

class Server(models.Model):
    name = models.CharField(max_length=120)

class ServerPropertie(models.Model):
    name = models.CharField(max_length=120)
    value = models.CharField(max_length=120)
    timestamp = models.DateTimeField()
    server = models.ForeignKey(Server)

我想将get_properties方法添加到服务器模型，该方法将返回当前服务器的所有最后一个属性。我的意思是，它应该返回当前服务器的所有属性名称的名称和值，并且每个uniq属性名称都应具有一个值，该行具有最大时间戳。

I want to add get_properties method to the Server model, which will return all last properties for current server. I mean it should return name and value of all properties names for current server and each uniq propertie name should have a value, which row has a maximum timestamp.

在原始硬编码的原始SQL中使用它（我使用postgres）：

I can do it in raw hardcoded raw SQL(i use postgres):

SELECT t1.name, t1.value FROM environments_serverpropertie t1
JOIN (SELECT max("timestamp") "timestamp", name 
      FROM environments_serverpropertie
      group by name) t2 on t1.name = t2.name and t1.timestamp = t2.timestamp;

或in python，但我相信存在pythonic解决方案。

or in in python, but i believe that there exists pythonic solution. Could you please help me.

推荐答案

如果您使用的是PostgreSQL，通常的语法是：

if you're using PostgreSQL, usual syntax for that is:

select distinct on (name)
    name, value
from environments_serverpropertie
where server = ...
order by name, timestamp desc

来自 PostgreSQL文档：

SELECT DISTINCT ON（expression [，...]）仅使给定表达式求值等于的每组行的
的第一行。
DISTINCT ON表达式使用与
ORDER BY相同的规则进行解释（请参见上文）。请注意，除非使用ORDER BY来确保所需的行
首先出现，否则每个集合的第一行都是
不可预测的。

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

您可以在 sql fiddle演示中查看并尝试 。

可以将此语法从 django文档：

仅在PostgreSQL上，可以按
传递位置参数（*字段），以指定DISTINCT应将
应用到的字段名称。

On PostgreSQL only, you can pass positional arguments (*fields) in order to specify the names of fields to which the DISTINCT should apply. This translates to a SELECT DISTINCT ON SQL query.

所以在Django中，它类似于：

So in django it will be something like:

ServerPropertie.objects.filter(...).order_by('name', '-timestamp').distinct('name')

这篇关于Django：选择具有最大时间戳记的值或加入同一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！