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.