[SOLVED] Get average of annotated fields in Django (postgres)

Get average of annotated fields in Django (postgres)

Consider the following models:

class Employee(models.Model):
    name = models.Charfield(max_length=100)

class Evaluation(models.Model):
    employee = models.ForeignKeyField(Employee, on_delete=models.CASCADE)
    question1 = models.PositiveIntegerField(default=0)
    question2 = models.PositiveIntegerField(default=0)

The idea is that an employee is reviewed, and we are recording a score for each question.

I can get the total score for an employee's evaluation by doing:

models.Evaluation.objects.annotate(score=F(question1) + F(question2))

which is fine, but I would really like to be able to get an employee's average score across multiple evaluations directly from the employee model.

I started with:

score_subquery = models.Evaluation.objects.annotate(score=F(question1) + F(question2)).filter(employee_id=OuterRef('pk').values('score')  
models.Employee.objects.annotate(avg_score=Avg(Subquery(score_subquery)))

This works fine until there is more than one evaluation for an employee, in which case we get:

more than one row returned by a subquery used as an expression

So I did some digging and came upon ArraySubquery

However, unfortunately this:

score_subquery = models.Evaluation.objects.annotate(score=F(question1) + F(question2)).filter(employee_id=OuterRef('pk').values('score')  
models.Employee.objects.annotate(avg_score=Avg(ArraySubquery(score_subquery)))

crashes with:

function avg(integer[]) does not exist

I assume that I have reached a postgres limitation?

I know I can get around this by using a real field to store the score, but I was just curious if this can be done strictly by using annotations?

Solution

You can work annotate with:

from django.db.models import Avg, F

models.Employee.objects.annotate(
    avg_score=Avg(F('evaluation__question1') + F('evaluation__question1'))
)