How to do SELECT MAX in Django?

I have a list of objects how can I run a query to give the max value of a field:

I'm using this code:

def get_best_argument(self):
    try:
        arg = self.argument_set.order_by('-rating')[0].details
    except IndexError:
        return 'no posts'
    return arg

rating is an integer

See this. Your code would be something like the following:

from django.db.models import Max
# Generates a "SELECT MAX..." query
Argument.objects.aggregate(Max('rating')) # {'rating__max': 5}

You can also use this on existing querysets:

from django.db.models import Max
args = Argument.objects.filter(name='foo') # or whatever arbitrary queryset
args.aggregate(Max('rating')) # {'rating__max': 5}

If you need the model instance that contains this max value, then the code you posted is probably the best way to do it:

arg = args.order_by('-rating')[0]

Note that this will error if the queryset is empty, i.e. if no arguments match the query (because the [0] part will raise an IndexError). If you want to avoid that behavior and instead simply return None in that case, use .first():

arg = args.order_by('-rating').first() # may return None

Django also has the 'latest(field_name = None)' function that finds the latest (max. value) entry. It not only works with date fields but also with strings and integers.

You can give the field name when calling that function:

max_rated_entry = YourModel.objects.latest('rating')
return max_rated_entry.details

Or you can already give that field name in your models meta data:

from django.db import models

class YourModel(models.Model):
    #your class definition
    class Meta:
        get_latest_by = 'rating'

Now you can call 'latest()' without any parameters:

max_rated_entry = YourModel.objects.latest()
return max_rated_entry.details

I've tested this for my project, it finds the max/min in O(n) time:

from django.db.models import Max

# Find the maximum value of the rating and then get the record with that rating. 
# Notice the double underscores in rating__max
max_rating = App.objects.aggregate(Max('rating'))['rating__max']
return App.objects.get(rating=max_rating)

This is guaranteed to get you one of the maximum elements efficiently, rather than sorting the whole table and getting the top (around O(n*logn)).

If you also want to get a value other than None in case the table is empty (e.g. 0), combine Max with Coalesce:

from django.db.models import Max, Value
from django.db.models.functions import Coalesce

max_rating = SomeModel.objects.aggregate(
    max_rating=Coalesce(Max('rating'), Value(0))
)['max_rating']

sol 01:

from .models import MyMODEL

max_rating = MyMODEL.objects.order_by('-rating').first()

sol 02:

from django.db.models import Max
from .models import MyMODEL

max_rating = MyMODEL.objects.aggregate(Max('rating'))

How to do SELECT MAX in Django?

Related

Recent Posts