getting rank of specific user with windows function in django

Using Django Using the ORM
1

this is my query which gives me the ranking of all user:

rank = Wallet.objects.filter(type=0, space_id=space.id).annotate(
            rank=Window(
                expression=DenseRank(),
                order_by=F('point_amount').desc(),
            )
        ).order_by('rank').values("user__id","user__username","rank")

but how can i get rank of specific user in this query?(i don’t want to handle this whit python)

i already try this:

rank = Wallet.objects.filter(type=0, space_id=space.id, user_id=user.id).annotate(
            rank=Window(
                expression=DenseRank(),
                order_by=F('point_amount').desc(),
            )
        ).order_by('rank').values("user__id","user__username","rank")

but it gives me rank 1 which is not correct

note:
this raw query gives me the right answer but i don’t konw how to convert this in to django orm query

query = f'''
            SELECT
            	FIND_IN_SET(
            		`transaction_wallet`.`point_amount`,
            		(
            			SELECT
            			GROUP_CONCAT(`transaction_wallet`.`point_amount` ORDER BY `transaction_wallet`.`point_amount` DESC)
            			FROM `transaction_wallet`
            			WHERE `transaction_wallet`.`type` = 0 AND `transaction_wallet`.`space_id` = {space.id}
            		)
            	) AS `rank`
            FROM
            	`transaction_wallet`
            WHERE
            	`transaction_wallet`.`type` = 0 AND `transaction_wallet`.`space_id` = {space.id} AND `transaction_wallet`.`user_id` = {self.id};
        '''
2

Just tossing out an idea, what happens if you apply the user_id filter as a separate filter after the annotate function? (And since you are only expecting to retrieve one value, you can remove the order_by function.)

Side note: If you remove the values functions, you can print the query attribute on the queryset to get a representation of the query that will be called.

3

if you mean this:

rank = Wallet.objects.filter(type=0, space_id=space.id).annotate(
            rank=Window(
                expression=DenseRank(),
                order_by=F('point_amount').desc(),
            )
        ).filter( user_id=user.id)

i have to say it still gives me rank 1 which is not correct.
i think it’s nature of window function that won’t be filtered.
but i wonder if there is another way

4

Thank you for asking this question! This is one of those where I had to learn a bit myself to be able to solve it.

First, this is a known issue in the current Django ORM. There have been a couple of tickets talking about this issue, and your question is not the first to have been raised on this topic.

What I have found is that this can be done using the django_cte package. The relevant section of the docs provide a starting point for this.

First, you do need to use a custom manager on your model.

Then, if you define your queryset:

rank_qs = Wallet.objects.filter(type=0, space_id=space.id).annotate(
            rank=Window(
                expression=DenseRank(),
                order_by=F('point_amount').desc(),
            )
        )

you can then use the cte functions to filter the results:

from django_cte import With

person_rank = With(rank_qs)
result = person_rank.queryset().with_cte(person_rank).get(user_id=user.id)
5

it worked.
thank you :pray: