CODEWITHSUNDEEP
djangodjango-querysetdjango-orm
8one6
8one6

Reputation: 13768

In Django ORM, get records whose field value is maximal in a queryset

Consider a simple model:

class Person(models.Model):
    name = models.CharField(max_length=256)
    age = models.IntegerField()

I would like a single expression which returns a QuerySet of all of the Person objects whose age is maximal in the table. I.e. say there are 20 Person records, and the largest age is 70 but there are 3 distinct records which have that value. I would like my queryset to contain exactly those 3 Person objects.

I suppose I could do:

Person.objects.filter(age=Person.objects.aggregate(models.Max('age'))['age__max'])

But wow that seems like a real mess. And it hits the database twice. Yuck.

Better alternatives?

Upvotes: 2

Views: 146

Answers (1)

Shang Wang
Shang Wang

Reputation: 25539

I can't directly answer your question, but I feel like you shouldn't beat up for getting this in one query, making your code clear is also important. So why don't you do:

from django.db.models import Max
# assumes that you have at least one record
max_age = Person.objects.aggregate(Max('age'))['age__max']
oldest_folks = Person.objects.filter(age=max_age)

Max would do MAX in sql statement, filter would do a simple sql lookup, none of the operations are awfully expensive.

Upvotes: 1

Related Questions