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.

1. context['avgs'] = Professor.objects.all().evaluation_set.all().annotate(avg_satisfactions=Avg('satisfaction'))
2. context['avgs'] = Professor.objects.prefetch_related().all().annotate(avg_satisfactions=Avg('satisfaction'))
3. 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.

CodePudding user response：

To annotate the average satisfaction you need to do

Professor.objects.annotate(avg_satisfaction=Avg("evaluation_names__satisfaction"))

"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 accessing current_professor.evaluation.all() does not make any additional queries.

CodePudding user response：

If you want to use only average data of relevant professor in detail page, you can filter like:

def get_context_data(self, *args, **kwargs):
     context = super().get_context_data(*args, **kwargs)
     prof_list = Professor.objects.annotate(avg_satisfaction=Avg("evaluation_names__satisfaction"))
     context['avg'] = prof_list.get(pk=self.kwargs['pk']).avg_satisfaction
     return context