#How to Rank Students Based on Total Points in Django QuerySet When Filtering Results

8 messages · Page 1 of 1 (latest)

edgy glacier
#

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.

twin pelican
#

```py

code

```

for colors in Discord

#

A QuerySet can chain filters, in your case the initial QuerySet is self within your StudentQuerySet methods. If you want to rank based on ALL student you need to change the base queryset that you are working from.

#

I am not sure it's a good idea to put that within a custom QuerySet class though, as it kind of contradicts the expected behavior. maybe just make that a helper function

#

you can however also just make a new QuerySet to filter on

#

not sure I understand your leaderbard, as

    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
        )

shows all Students except the top 3 ones by rank? does that make any sense?

edgy glacier