I'm trying to build a game hiscore view with rankings for my Django site, and I'm having some issues.
The query I have is the following:
row_number_rank = Window(
expression=RowNumber(),
partition_by=[F('score_type')],
order_by=F('score').desc()
)
hiscores = Hiscore.objects.annotate(rank=row_number_rank).values()
The query above works perfectly, and properly assigns each row a rank according to how it compares to other scores within each score type.
The result of this is the following:
{ 'id': 2, 'username': 'Bob', 'score_type': 'wins', 'score': 12, 'rank': 1 }
{ 'id': 1, 'username': 'John', 'score_type': 'wins', 'score': 5, 'rank': 2 }
{ 'id': 4, 'username': 'John', 'score_type': 'kills', 'score': 37, 'rank': 1 }
{ 'id': 3, 'username': 'John', 'score_type': 'kills', 'score': 5, 'rank': 2 }
{ 'id': 5, 'username': 'Bob', 'score_type': 'kills', 'score': 2, 'rank': 3 }
The issue comes in when I want to retrieve only a specific user's scores from the above results. If I append a filter(username="Bob") the query is now:
row_number_rank = Window(
expression=RowNumber(),
partition_by=[F('score_type')],
order_by=F('score').desc()
)
hiscores = Hiscore.objects.annotate(rank=row_number_rank).filter(username='Bob').values()
Unexpectedly, adding this filter step has yielded the following incorrect results:
{ 'id': 2, 'username': 'Bob', 'score_type': 'wins', 'score': 12, 'rank': 1 }
{ 'id': 5, 'username': 'Bob', 'score_type': 'kills', 'score': 2, 'rank': 1 }
Randomly, the rank on the id=5 entry has decided to change to 1 instead of its correct value of 3.
Why would adding this filter step modify the values of the fields in the QuerySet, instead of just excluding the proper elements from it?
Thanks.
