ChatGPT解决这个技术问题 Extra ChatGPT

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


S
Sasha Chedygov

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

I need the actuall argument object that has that Max, so I can print the details field. The args.aggregate(Max('rating')) call just returns the highest rating. I'm looking for the arg with the highest rating.
What's wrong with your exiting code - Argument.objects.order_by("-rating")[0]?
L
Lutz Schönemann

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

This is a great way of using latest(). If you need the record with the minimum value, you can use earliest().
latest() and earliest() works with non-date field too, but it is a side-effect of the implementation. You should use <your-queryset>.order_by('<interested-field>').first() or <your-queryset>.order_by('<interested-field>').last() to ensure your code will still works even if Django developers will change latest() and earliest() implementation to work with date fields only.
This is a bad answer: 1) as already noticed earlier it's detail of implementation and can be changed in the future 2) it's not very readable - max number is not necessarily latest saved one (or in any other sense)
f
funnydman

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


Big-O sometimes doesn't bear out in practice, especially for smaller n, where coefficients and implementation matter. Your code is in python/django, which is compiled to bytecode and may or may not be optimized. The database is likely written in a language that is optimized and compiled to machine instructions. Besides, the database has no real reason to sort, if the function is only looking for a max value. I'd like to see timing data before I'm comfortable using hand-build code over a built-in database function.
@afahim I don't think this the code you post is completely correct. if turns out you have more than one maximum (let's say you have two Apps with 5 stars) using "get" will raise an error.
Another issue with this approach is that in general there is no guarantee that after computing the maximum the record with it has not been deleted before querying it. To ensure that, you would need to execute these two database statements in a transactions while locking the whole table using select_for_update, which has a major performance impact on the database.
D
Dipta Dhar

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

r
roskakori

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

S
Sona Pochybova

To maybe improve on @afahim answer with regards to @Raydel Miranda comment, if you want a random comment. If you want all, then use just the filter

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.filter(rating=max_rating).first()

Z
ZeQ

maybe it will help someone's trouble

def get_queryset(self):
    sorgu = Sunum.objects.values('id', 'firma', 'projeadi', 'sunumdurum__durum', 'sunumdurum__aciklama'
                           ).annotate(max_rank=Max('sunumdurum__kayittarihi'))
    szlk={}
    for sor in sorgu :
        ana = sor['id'], sor['firma'], sor['projeadi']
        dana = sor['sunumdurum__durum'], sor['sunumdurum__aciklama'], sor['max_rank']
        szlk.setdefault(ana, dana)
    return szlk