Question 113986 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to calculate the values of child-tables by using ForeignKey from a parent-table

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search