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)
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.