Django: how to annotate based on another annotation, or perform operations on an annotation

Question

So I am making something that organizes a school schedule. The school in question is organized in a way that the classes stay together in the same room and the teacher moves, the schedule is different every day, and not all the teachers or the classes are in the building in at the same time, and some of the teachers are very picky about when they teach.

I have these models:

class Teacher(models.Model):
    christian_name = models.CharField(max_length=200)
    family_name = models.CharField(max_length=200)
    ...

class TeacherAvailableHour(models.Model):
    teacher = models.ForeignKey('Teacher')

class Requirement(models.Model):
    ...
    teacher = models.ForeignKey('Teacher')
    per_week = models.PositiveIntegerField()
    ...

So the thing I am trying to do is make a schedule for the pickiest teachers first, namely the ones with the smallest ratio between when they can teach (total "TeacherAvailableHour"s) and the number of hours they are required to teach (the sum of all of the teacher's "Requirement.per_week").

In other words, I need to "order_by" that ratio. I can't think of how to do it. Tried this:

Teachers=Teacher.objects.annotate(availability=(Count('teacheravailablehour') / Sum('requirement__per_week')).order_by('availability')

That blows up with an error, of course. I've also tried something like a double annotation:

Teachers=Teacher.objects.annotate(availability=Count('teacheravailablehour')).annotate(required=Sum('requirement__per_week')).annotate(availRatio=(('availability') / ('required')).order_by('-availRatio')

What is the best way to do this? Is this even possible?

Answer 1

You cannot calculate such a ratio using the annotate method. You need to use the extra() method provided by the QuerySet API. While lookups using extra are not advisable as they are not portable across DB engines, they are sometimes the best (or only) option for doing such complex queries.

Assuming all the models you are referencing are in an app named schedules, and you have not modified table names using the db_table attribute in your models' metaclasses, the lookup would be something like this:

availability_sql = "(select count(*) from schedules_teacheravailablehour where schedules_teacheravailablehour.teacher_id=schedules_teacher.id) * 1.0"
required_hrs_sql = "(select sum(per_week) from schedules_requirement where schedules_requirement.teacher_id=schedules_teacher.id)"
Teacher.objects.extra(select={"ratio":"%s/%s" %(availability_sql, required_hrs_sql)}).order_by("-ratio")