Filtering Django Query by the Record with the Maximum Column Value

Question

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.

e.g.

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)

with the records:

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.

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?

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

Answer 1

Update : if you are using PostgreSQL, you can use the ORM with .distinct()

From PostgreSQL documentation:

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.

Using the Django ORM:

Person.objects.order_by('name', '-created').distinct('name')

Generated SQL:

select distinct on (name)
    ...
from person_person
order by name, created desc

Answer 2

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
''')