My purpose is to gain averages of values stored in child-tables depending on each parent-table.
In my case, I want to gain averages of satisfaction stored in the Evaluation model (child) depending on each Professor model (parent).
models.py
class Professor(models.Model):
college = models.CharField(max_length=50, choices=COLLEGE_CHOICES)
name = models.CharField(max_length=50)
def __str__(self):
return self.name
class Evaluation(models.Model):
name = models.ForeignKey(Professor, on_delete=models.CASCADE, related_name='evaluation_names', null=True)
satisfaction = models.IntegerField(choices=SATISFACTION_CHOICES)
def __str__(self):
return self.comment
views.py
class ProfessorDetail(generic.DetailView):
model = Professor
context_object_name = "professor_detail"
template_name = "professors/professor_detail.html"
def get_context_data(self, *args, **kwargs):
context = super().get_context_data(*args, **kwargs)
context['avgs'] = Professor.objects.all().evaluation_set.all().annotate(avg_satisfactions=Avg('satisfaction'))
return context
professors/professor_detail.html
{% for evaluation in avgs %}
<p>{{ evaluation.avg_satisfactions }}</p>
{% endfor %}
I tried following codes for views.py.
context['avgs'] = Professor.objects.all().evaluation_set.all().annotate(avg_satisfactions=Avg('satisfaction'))
context['avgs'] = Professor.objects.prefetch_related().all().annotate(avg_satisfactions=Avg('satisfaction'))
context['avgs'] = Professor.objects.all().prefetch_related(Prefetch('evaluation_set', queryset=Evaluation.objects.all().annotate(avg_satisfactions=Avg('satisfaction'))))
But, all of them do not work.
2
Answers
To annotate the average satisfaction you need to do
"evaluation_names" is the related table name and "satisfaction" is the column to compute average from.
I also want to make the use of prefetch_related clear since you were try to use it to solve your issue.
The prefetch_related method is used to reduce the number of query when iterating over a queryset.
Lets imagine you wanted to display every evaluation for every professor. You would access
current_professor.evaluation.all()
for current_professor in Professor.objects.all()
.This would generate an sql query equivalent to
Evaluation.objects.filter(name=current_professor)
for each professor, resulting in n+1 DB request (with n the number of professor).Prefetch_related is here to avoid this issue,
Professor.objects.prefetch_related(Prefetch("evaluation_names"))
will prefetch and cache relevant queries so that accessingcurrent_professor.evaluation.all()
does not make any additional queries.If you want to use only average data of relevant professor in detail page, you can filter like: