Get average of annotated fields in Django (postgres)

Question

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?

score 2 · Accepted Answer · answered Jul 18 '23 at 15:10

2

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

answered Jul 18 '23 at 15:10

Willem Van Onsem

443,496
30
428
555

Guess I was just overthinking it! Thanks dude. I am still curious if it is possible to do this by only referencing the 'score' annotation on the Evaluation model. In practice my use case has many more questions, and it would be nice if in future updates I only needed to modify the one annotation instead of two. Not going to cry about it though. – DrS Jul 18 '23 at 15:29
@DrS: I don't immediately see a way to do this, no. Perhaps through `.alias(..)` but I can't test it at the moment, and I would expect that it would not group per `Empolyee`, but per `Evaluation` in that case. – Willem Van Onsem Jul 18 '23 at 17:53

Get average of annotated fields in Django (postgres)

1 Answers1