Warm tip: This article is reproduced from serverfault.com, please click

Keeping annotated rank when filtering Django

发布于 2020-11-28 06:20:31

I have a filtered Queryset where I have annotated the rank of each object according to a specific order.

When I then perform other filters like changing the order by the rank stays the same. The problem comes when perfoming a new .filter()

I have a search function on the page that filters the results after the title of the objects. But when I do this the annotated rank value changes depending on how many results are in the new query instead of keeping its original value.

To illustrate:

This is my original query:

choices_filter = Choice.objects.filter(tournament=pk).annotate \
    (rank=RawSQL("RANK() OVER(ORDER BY winrate DESC, pickrate DESC, times_played)", []))

This returns a queryset like this:

Rank        Title         Winrate      
1...........Apple...........55%
2...........Pear............47%
3...........Banana..........44%
4...........Orange..........35%
5...........Watermelon......31%

If I perform a .order_by('title') I get the expected result of:

Rank        Title         Winrate      
1...........Apple...........55%
3...........Banana..........44%
4...........Orange..........35%
2...........Pear............47%
5...........Watermelon......31%

But if I instead perform a .filter(title__icontains='an') I get this:

Rank        Title         Winrate      
1...........Banana..........44%
2...........Orange..........35%

Instead of the desired:

Rank        Title         Winrate      
3...........Banana..........44%
4...........Orange..........35%

I am still not experienced enough with Django and python (and databases) to navigate through this on my own. It took me a while to figure out how to annotate the rank and have it working with pagination etc.

This is my full View code if relevant (I am using Django Rest Framework and APIviews):

class GameDetailFilterView(APIView, PaginationHandlerMixin):
    permission_classes = (AllowAny, )
    pagination_class = FullPagination
    serializer_class = ChoiceSerializer

    def get(self, request, pk, *args, **kwargs):
        choices_filter = Choice.objects.filter(tournament=pk).annotate \
            (rank=RawSQL("RANK() OVER(ORDER BY winrate DESC, pickrate DESC, times_played)", []))
        filter_condition = request.session['filter_condition']
        if filter_condition is not None:
            choices = choices_filter.filter(title__icontains=filter_condition)
        else:
            choices = choices_filter.order_by('rank')

        page = self.paginate_queryset(choices)

        if page is not None:
            serializer = self.get_paginated_response(self.serializer_class(page,
                                                                           many=True).data)
        else:
            serializer = self.serializer_class(choices, many=True)
        return Response(serializer.data, status=status.HTTP_200_OK)
Questioner
Josie
Viewed
0
ahmadgh74 2020-11-28 17:42:34

You can use django-cte library

from django_cte import CTEManager, With

# models.py
class Choice(Model):
    objects = CTEManager()
    # ... other fields like tournament

# query 
cte = With(
Choice.objects.filter(tournament=pk).annotate \
(rank=RawSQL("RANK() OVER(ORDER BY winrate DESC, pickrate DESC,times_played)", [])))
qs = cte.queryset().with_cte(cte).filter(title__icontains='an')

and follow this link.