Django self join，如何将该查询转换为ORM查询 [英] Django self join , How to convert this query to ORM query

问题描述

如何将该查询转换为Django ORM查询.

How can i convert this query to django ORM query.

select T.node_id, ht, status, data from (
select id, Max(health_time) as ht, node_id from remote_sense_nodehealth group by node_id
) as T
join remote_sense_nodehealth on remote_sense_nodehealth.health_time=T.ht and remote_sense_nodehealth.node_id = T.node_id

实际上，我想基于其他列值获取所有最新值.

Actually i want to get all the latest value based on other column value.

例如，我的桌子就像-

c1 | c2  | c3 
- - - - - - -
x | 1 AM | d1
x | 2 AM | d2
x | 3 AM | d3
y | 1 AM | d4
y | 2 AM | d5{

所需的输出:

[{c1:x，c2:3AM，c3:d3}，{c1:y，c2:2AM，c3:d5}]

[{c1: x, c2: 3AM, c3: d3}, {c1: y, c2: 2AM, c3: d5}]

推荐答案

使用更加规范化的数据模型，您将更轻松地进行此操作.考虑使用这样的方法:

You'll have an easier time doing this with a more normalized data model. Consider using an approach like this:

class NodeGroup(model.Model):
    pass

class NodeHealth(model.Model):
    node_group = models.ForeignKey(NodeGroup, related_name='nodes')
    health_time = models.IntegerField()
    status = models.IntegerField()

然后您可以执行以下操作:

Then you could do this:

from django.db.models import Max, F

nodes = NodeHealth.objects.all().annotate(
    max_health_time=Max('node_group__nodes__health_time')
).filter(health_time=F('max_health_time'))

不幸的是，此时，如果多个节点的health_time值相同，则返回的节点将具有重复项.您也许可以添加.distinct('node_group_id')来清除它，但是我不是100％肯定的.

Unfortunately at that point, the nodes returned will have duplicates based if more than one node has the same value for health_time. You might be able to add a .distinct('node_group_id') that could clear that up, but I'm not 100% positive.

这篇关于Django self join，如何将该查询转换为ORM查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！