django annotate a function that returns the maximum of a field among all objects that has a feature

Question

Suppose that I have this model:

class Student(models.Model):
    class_name = models.CharField()
    mark = models.IntegerField()

And I want to get all the students that have the highest mark in their class. I can get the student who has the highest mark in all the classes like it is mentioned in this post. But I want all the students that have the highest mark in their class, something like this:

Student.objects.annotate(
    highest_mark_in_class=Max(
        Students.objects.filter(class_name=F('class_name'))
        .filter(mark=highest_mark_in_class)
    )
)

I can do this with a for loop, but with a large database for loops are rather slow. I don't know if it's possible to write such a query in one line?

Answer 1

You will have to use 2 queries for that:

import operator
from functools import reduce
from django.db.models import Max, Q

best_marks = Student.objects.values('class_name').annotate(mark=Max('mark'))
q_object = reduce(operator.or_, (Q(**x) for x in best_marks))
queryset = Student.objects.filter(q_object)

First query gets a list of best mark for each class.

Second query gets all students that where mark and class matches one item of the list.

Note that if you call .annotate(best_mark=Max('mark')) instead of .annotate(mark=Max('mark')), you will have to do some extra work to rename best_mark as mark prior to passing the dictionnary to the Q object. While Q(**x) is quite convenient.