Filtering Django Query by the Record with the Maxi

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

If your backend is PostgreSQL Roman Pekar gave a good answer for this question.

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