通过具有最大列值的记录过滤Django查询 [英] Filtering Django Query by the Record with the Maximum Column Value

问题描述

根据哪个记录在列中具有最大/最小值是否有简单的方法过滤Django查询？我本质上是要求这些 问题，但在Django的ORM的具体上下文中。

Is there an easy way to filter a Django query based on which record has a max/min value in a column? I'm essentially asking these questions, but in the specific context of Django's ORM.

例如

说我有一个设计来存储的模型每个人的电话号码的历史价值。

Say I have a model designed to store the historical values of everyone's phone numbers.

class Person(models.Model):
    name = models.CharField(max_length=100)
    phone = models.CharField(max_length=100)
    created = models.DateTimeField(auto_now_add=True)

与记录：

Person(name='Jim',phone='123-456-9870', created=datetime(2005,1,2,4,2))
Person(name='Jim',phone='329-802-9870', created=datetime(2006,9,2,7,8))
Person(name='Sue',phone='324-345-3450', created=datetime(2008,7,4,6,1))

现在说我想查找每个人的最新电话号码。

Now say I wanted to find everyone's most recent phone number.

在SQL中，我通常必须使用子查询来计算最大值：

In SQL, I'd usually have to use a subquery to calculate the maximum values:

SELECT p1.name, p1.phone, p1.created
FROM person_person p1, (
    SELECT name, MAX(created) AS max_created
    FROM person_person
    GROUP BY name
) AS p2
WHERE p1.name = p2.name AND p1.created = p2.max_created

Is there any mechanism in Django that could simplify this?

我在后台使用PostgreSQL，所以任何依靠PostgreSQL特定功能的想法或解决方案将有帮助。

I'm using PostgreSQL on my backend, so any thoughts or solutions that would rely on PostgreSQL specific functionality would be helpful.

推荐答案

您可能只想在这里使用原始SQL， raw（） 管理员方法有利于此，允许你从哟返回模型实例ur查询。唯一的诀窍是原始查询需要包含主键。这可能适用于您（除非您将主键设置为除 id 之外的其他内容）：

You'll probably just want to use raw SQL here, the raw() manager method facilitates this, allowing you to return model instances from your query. The only trick is that the raw query needs to include the primary key. This should probably work for you (unless you have the primary key set to something other than id):

latest_phone_numbers = Person.objects.raw('''
SELECT p1.id, p1.name, p1.phone, p1.created
FROM person_person p1, (
    SELECT name, MAX(created) AS max_created
    FROM person_person
    GROUP BY name
) AS p2
WHERE p1.name = p2.name AND p1.created = p2.max_created
''')

这篇关于通过具有最大列值的记录过滤Django查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！