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

Using Django Using the ORM
1

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.

2

You could use a subquery to compute the rank over all students, then filter the results based on the parent. Something like that with Window functions :

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

    # Subquery to compute rank over all students
    rank_subquery = Student.objects.all().with_points().annotate(
        rank=models.Window(
            expression=models.functions.Rank(),
            order_by=models.F("total_points").desc(),
        )
    ).filter(id=OuterRef('id')).values('rank')

    # Annotate the queryset with the rank
    queryset = queryset.annotate(rank=Subquery(rank_subquery))

    queryset = self.filter_queryset(queryset)
    return queryset
3

this returns me the same thing, but the rank numbers are still incorrect.

{
    "count": 2,
    "next": null,
    "previous": null,
    "results": [
        {
            "id": 16,
            "first_name": "asd",
            "last_name": "asd",
            "total_points": 0,
            "rank": 1,
            "me": true
        },
        {
            "id": 17,
            "first_name": "uiin",
            "last_name": "uho",
            "total_points": 0,
            "rank": 1,
            "me": true
        }
    ]
}

There is another student who scored 300 points. And the rank of this students are not correct

4

I found the sollution. Just need to use django-cte library.

from django_cte import CTEManager, CTEQuerySet

class StudentQuerySet(SetFieldQuerySetMixin, CTEQuerySet, models.QuerySet):
    ...

class StudentManager(CTEManager, models.Manager):
    ...

and also in view change get_queryset() like this

def get_queryset(self):
    all_students = Student.objects.all().with_rank()
    student_ranks = With(all_students)
    result = (
    student_ranks.queryset()
        .with_cte(student_ranks)
        .filter(parent=self.request.user, rank__gt=3)
    )
    queryset = self.filter_queryset(result)
    return queryset
1 Like