Get average of averages in django

Question

I have three models like these

    class School(models.Model):
      # some fields

    class Class(models.Model):
       school = models.ForeignKey(School, related_name='classes')

    class Student(models.Model):
       class = models.ForeignKey(Class, related_name='students')
       degree = models.IntegerField()

I want to order schools using average degree , but the average degree Isn't just the average of all the students as the number of the students in the same class are not equal

so I need to get some thing like this

from django.db.models import Prefetch, Avg
classes = Prefetch('classes', Class.objects.all().annotate(avg_degree=Avg('students__degree'))


# this line would through an error
qs = School.objects.all().prefetch_related(classes)
.annotate(avg_degree=Avg('classes__avg_degree')).order_by('avg_degree')

of course I can't just use

qs = School.objects.all().annotate(avg_degree=Avg('classes__student__degree'))

this would give wrong answers

Answer 1

Unfortunately, you cannot annotate on columns that are created using another annotate. Prefetch does not provide access to annotate'd fields, it's primarily used to filter. If you don't need to operate on that queryset further (need only values), you can use raw query solution:

from django.db.models import Avg, F

query = (
    Class.objects
    .annotate(avg_degree=Avg('students__degree'), s_id=F('school'))
    .values('s_id', 'deg')
    .query
)
sql, params = query.sql_with_params()
template = '''
SELECT 
    temp.s_id as id, 
    AVG(temp.deg) as degree 
FROM ({}) "temp" 
GROUP BY temp.s_id
'''
qs = School.objects.raw(template.format(sql), params)

# Now we have required field:
for obj in qs:
    print(obj.id, obj.degree)