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