Django模型根据其他列的最大值获取独特的外键行 [英] Django model get unique foreign key rows based on other column max value

问题描述

我是Django的新手，我正在尝试创建一个简单的应用程序来跟踪SQlite数据库中不同的服务器配置。我创建了两个数据库模型：

I'm new to Django, and I'm trying to create a simple application that keep track on different server configurations in a SQlite database. I've created 2 database models:

from django.db import models

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

class Config(models.Model):
    server = models.ForeignKey(Server, on_delete=models.CASCADE)
    configuration = models.CharField(max_length=250)
    config_version = models.IntegerField()

以下是2个模型示例数据：

Here are the 2 models sample data:

Server:
| id     | name    |
| ------ | ------  |
| 1      | Server1 |
| 2      | Server2 |
| 3      | Server3 |

Config:
| id     | configuration | config_version | server |
| ------ | ------------- | -------------- | ------ |
| 1      | srv1_cfg1     | 1              | 1      |
| 2      | srv2_cfg1     | 1              | 2      |
| 3      | srv2_cfg2     | 2              | 2      |
| 4      | srv2_cfg3     | 3              | 2      |
| 5      | srv3_cfg1     | 1              | 3      |
| 6      | srv1_cfg2     | 2              | 1      |
| 7      | srv1_cfg3     | 3              | 1      |

我想查询配置表，只得到最大值为config_version的行每个服务器ID的字段，如：

I would like to query the Config table, and get only rows with the maximum value of "config_version" field for each server id, like:

Desired result:
| id     | configuration | config_version | serverid | servername |
| ------ | ------------- | -------------- | -------- | ---------- |
| 4      | srv2_cfg3     | 3              | 2        | Server2    |
| 5      | srv3_cfg1     | 1              | 3        | Server3    |
| 7      | srv1_cfg3     | 3              | 1        | Server1    |

我尝试过许多不同的选项来构建正确的查询，但到目前为止，我无法得到什么想。我最好的结果是查询服务器表：

I've tried many different options to construct the correct query, but so far I cannot get what I want. My best result is to query the Server table:

Server.objects.annotate(maxver=Max('config__config_version'))

但是看起来我无法访问Config表对象，所以我想我需要查询配置表中有一些过滤？

But it seems I cannot get access to the Config table objects, so I guess I need to query the Config table with some filtering?

我可以用原始的SQL查询来做到这一点，但是我更愿意以Django的方式做。任何帮助将不胜感激。

I can do this with a raw SQL query, but I would strongly prefer to do it the "Django" way. Any help will be much appreciated.

推荐答案

经过一些更多的努力，我来了一个适合我的解决方案。我确定它不是最佳的，但至少似乎有效：

After some more struggle with this, I've came with a solution that works for me. I'm sure it is not optimal, but at least seems to works:

from django.db.models import Max, F

s1 = Config.objects.annotate(maxver=Max('server__config__config_version'))
config_list = s1.filter(config_version=F('maxver'))

如果有更好的方法来做，我很想知道。

If there is a better way to do this, I would love to know it.

这篇关于Django模型根据其他列的最大值获取独特的外键行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！