[Fixed]-GROUP BY in django ORM with synthetic attributes

by

1👍

Have you thought about putting this logic on the model? This makes the business logic available anywhere the Student object is. Consider:

class Student(models.Model)
    ...

    @property
    def average_test_grade(self):
        return self.testgrade_set.all().annotate(
            score=Case(
                When(letter_grade="A", then=Value(100)),
                When(letter_grade="A-", then=Value(90)),
                When(letter_grade="B+", then=Value(89)),
                When(letter_grade="B-", then=Value(80)),
                default=Value(0),
                output_field=IntegerField()
            )
        ).aggregate(Avg('score'))

Then in your views.py you can reduce the number of queries with .prefetch_related():

students = Student.objects.prefetch_related('testgrade_set').all()

and finally in the template or potentially elsewhere:

<ul>
{% for student in students %}
<li>{{ student.average_test_grade }}</li>
{% endfor %}
</ul>
👤Scott Woodall

Leave a comment