Hello fellow django devs please I need help on a project I’m working on. It’s a project to automate the result computation for high school students. Below are my models and views of what I have done so far.
I want to list students created by a user(teacher) and order/sort them based on the aggregate of the first_test, second_test and exam scores.
class Student(models.Model):
author = models.ForeignKey(User, null=True, blank=True, on_delete=models.SET_NULL)
name = models.CharField(max_length=200)
level = models.CharField(max_length=50, choices=CLASS, default=CLASS[0][0])
def __str__(self):
return self.name
def get_absolute_url(self):
return reverse("student-detail", kwargs={"pk":self.pk})
class Score(models.Model):
student = models.ForeignKey(Student, on_delete=models.CASCADE)
subject = models.CharField(max_length=50, choices=SUBJECTS, default=SUBJECTS[0][0], blank=True, null=True)
first_test = models.PositiveIntegerField(validators=[MaxValueValidator(15, message="must not be more than 15")], blank=True, null=True)
second_test = models.PositiveIntegerField(validators=[MaxValueValidator(15, message="must not be more than 15")], blank=True, null=True)
exam = models.PositiveIntegerField(validators=[MaxValueValidator(70, message="must not be more than 70")], blank=True, null=True)
class StudentDetailView(DetailView):
model = Student
template_name = "check/student_detail.html"
context_object_name = "student"
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
context["student_scores"] = Score.objects.filter(student__id=self.kwargs.get("pk"))
context["total"] = Score.objects.filter(student__id=self.kwargs.get("pk")).annotate(sum_test=F("first_test") + F("second_test") + F("exam")).aggregate(total=Sum("sum_test"))
context["average"] = Score.objects.filter(student__id=self.kwargs.get("pk")).annotate(sum_test=F("first_test") + F("second_test") + F("exam")).aggregate(average=Avg("sum_test"))
return context
class StudentListView(LoginRequiredMixin, ListView):
model = Student
template_name = "check/student_list.html"
context_object_name = "students"
def get_queryset(self):
return Student.objects.filter(author=self.request.user)
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
context["student_scores"] = Score.objects.filter(student__id=self.kwargs.get("pk"))
context["total"] = Score.objects.filter(student__id=self.kwargs.get("pk")).annotate(sum_test=F("first_test") + F("second_test") + F("exam")).aggregate(total=Sum("sum_test"))
context["average"] = Score.objects.filter(student__id=self.kwargs.get("pk")).annotate(sum_test=F("first_test") + F("second_test") + F("exam")).aggregate(average=Avg("sum_test"))
return context
Hi @4tolexx — Welcome.
Take a read of the docs on Aggregations
https://docs.djangoproject.com/en/3.1/topics/db/aggregation/
One example from the docs is this:
>>> Author.objects.annotate(average_rating=Avg('book__rating')).values('name', 'average_rating')
You’d need to do similar annotating the sum you need, and them ordering by that.
Have a play, see how far you get. Happy to look a bit more later on.
Kind Regards,
Carlton
1 Like
@carltongibson thanks for your help and response. I really appreciate. I have used the snippet you provided and also read the django docs.
So far I have been able get the sum of first test, second test and exam using the annotate function and also do an order_by per student.
But I want to get an aggregate of first test, second test and exam per student and order based on the aggregate value
This is what I have tried below but not working as expected
Student.objects.values(“name”).annotate(sum=F(“score__first_test”) + F(“score__second_test”) + F(“score__exam”).aggregate(total=Sum(“sum”).
The aggregate function at the end gives the total for all the students created by a user but I want it to be based on per student. I will also order based on the aggregate.
Thanks in advance and I look forward to your response.
The .aggregate() at the end is what’s causing you to aggregate across all students as aggregrate will return 1 value for each argument specified. Using only the .annotate() that you’ve defined should return the value you want as sum on each instance of Student.
@CodenameTim thanks for responding.
The idea is a student has three different scores for a particular subject based on my model above. The annotate() function gives the total score for a particular subject. aggregate() function sums all the total from annotate().
I want to list all students created by a user and order them based on the sum of all the totals per student
I’m using inline formset factory to enter the three different scores per student.
I’m just currently stuck on ordering based on sum of totals per student.
Hi @4tolexx — the aggregate is mapping to an SQL GROUP BY… (as @CodenameTim says, that’s causing the rows to collapse).
It sounds like you need to add annotations for each test, and then a further annotation for the sum:
So I’d address them one at a time. You can use DB functions, like Sum in annotations. Those aggregate in a sense but that’s not what aggregate() is up to (which, again, is GROUP BY).
@carltongibson thanks a lot. I really appreciate your patience.
I finally got it to work the way I want this morning following your suggestions. This is what I did:
Student.objects.values(“name”).annotate(sum=F(“score__first_test”) + F(“score__second_test”) + F(“score__exam”).values("name").annotate(total=Sum("sum").order_by("-total")
The challenge I’m facing now is presenting on the template. What I have tried below:
{% for student in my_student %}
<a href={% url 'student-detail' student.id>{{ student.name}} </a>
{% endfor %}
When I try the above I get the NoRevereseMatch error from student details
Here is my url
path('student-detail/<int:pk>', views.StudentDetailView.as_view(), name='student-detail')
1 Like
Super. Glad you got it working! 
This is your URL name not matching — did you include it with an app_name maybe? 
(If so the URL name will be app_name:student-detail since the URL will have an namespace.
@carltongibson thanks again. I’m so happy I got it working.
I’m not using a namespace.
This is my view:
class StudentListView(LoginRequiredMixin, ListView):
model = Student
template_name = "check/student_list.html"
context_object_name = "students"
def get_queryset(self):
return Student.objects.filter(author=self.request.user)
def get_context_data(self, **kwargs):
context = super().get_context_data(**kwargs)
context["my_student"] = Student.objects.filter(author=self.request.user).values(“name”).annotate(sum=F(“score__first_test”) + F(“score__second_test”) + F(“score__exam”).values("name").annotate(total=Sum(“sum”).
return context
So in the template I iterated over my_student context.
Presenting it in the template is what needs to be solved now.
The error isn’t about your view. It’s about your urls.py.
What’s the exact error?
@carltongibson this is the error I’m getting below:
NoReverseMatch at /student-list/
Reverse for ‘student-detail’ with arguments ‘(’ ‘,)’ not found. 1 pattern(s) tried: [student\-detail/(?P[0-9]+)$’]
Assuming it’s not a copy-paste error, you don’t have the closing %} on your url tag.
You probably want something that looks more like this:
<a href="{% url 'student-detail' student.id %}">{{ student.name}} </a>
@KenWhitesell thanks for your response.
That’s actually a copy and paste error. My navigation tag is actually written the way you wrote it.
I’m currently working to solve the url problem now. I’m just kinda confused why it’s not working because it should be working.
I don’t know if it’s a regex issue.
Would love to hear your thoughts thanks
So the error message is actually indicating that you’re not passing the parameter to the url method. It’s implying that student.id is null - which obviously isn’t the case (Or shouldn’t be, anyway).
@KenWhitesell student.id isn’t null because when I replace student.id in the template I get a result.
But that’s not the way it should work. It should display list of students based on the query set
I’m having a difficult time reconciling what you’re saying with what I’m seeing based on your code.
You write:
but then you post:
context["my_student"] = Student.objects.filter(author=self.request.user).values(“name”).annotate(sum=F(“score__first_test”) + F(“score__second_test”) + F(“score__exam”).values("name").annotate(total=Sum(“sum”).
Where you specify that you’re supplying a dict to the context by use of the values clause, but you’re not selecting the id field to include.
My first reaction was going to be that you needed to add 'id` to your values clause, but you’re saying it’s not necessary because you get a value…
I think there’s something else missing here - either another copy-paste error, or something that has been excluded from your view or template that is masking the real situation.
@KenWhitesell wowwwwwwwwwwww!!! You just saved me like Jesus christ. I’m so glad.
I just added Id to the values() function like you just pointed out.
It worked like magic 
Honestly thanks a lot
earlier I made a mistake in my post. Thanks a million