[Python] How to Rank Students Based on Total Points in Django QuerySet When Filtering Results

I'm working on a Django application where I need to rank students based on their total points and then filter the results. I want to rank all students based on their total points but only show students who are children of a particular user (supervisor).

class StudentQuerySet(SetFieldQuerySetMixin, models.QuerySet):
"""QuerySet for Student model"""

def with_points(self) -> Self:
"""Adds a field indicating total points of a student."""
return self.annotate(
total_points=Coalesce(
models.Sum("attendances__feedback__overall"), Value(0)
)
)

def with_rank(self) -> Self:
"""Adds a field indicating rank of a student."""
self = self.with_points()

return self.annotate(
rank=models.Window(
expression=models.functions.Rank(),
order_by=models.F("total_points").desc(),
)
)



class StudentLeaderboardMeListView(generics.ListAPIView):
serializer_class = StudentLeaderboardSerializer
permission_classes = [IsSupervisor]
filter_backends = (DjangoFilterBackend,)
filterset_class = StudentFilter

def get_queryset(self):
all_students = Student.objects.all().with_rank()
top_3_ids = all_students.order_by("rank")[:3].values_list(
"id", flat=True
)
queryset = all_students.exclude(id__in=top_3_ids).filter(
parent=self.request.user
)
queryset = self.filter_queryset(queryset)
return queryset

def list(self, request, *args, **kwargs):
queryset = self.get_queryset()

page = self.paginate_queryset(queryset)
if page is not None:
serializer = self.get_serializer(page, many=True)
return self.get_paginated_response(serializer.data)

serializer = self.get_serializer(queryset, many=True)
return Response(serializer.data)


The code correctly ranks all students based on their total points, but the ranking is affected by the subsequent filtering (exclude(id__in=top_3_ids).filter(parent=self.request.user)). I need to ensure that the ranking is computed based on all students before applying the filter. Essentially, the ranking should not change based on the filtered results.

How can I ensure that the ranking is computed over the entire dataset of students, regardless of the subsequent filtering? I need the rank to be calculated based on all students, but still be able to filter and display students who are children of the logged-in supervisor.

Continue reading...