CODEWITHSUNDEEP
djangodjango-queryset
darl1ne
darl1ne

Reputation: 416

Anotation after filter (freeze queryset) Django

In the process of model annotation there is a need to filter the finished list. But the value of the annotation "rank" after the filter() becomes "1" because it has only one element. Without filtering the queryset everything works fine

request_user = (
            MainUser.objects.select_related("balance_account")
            .annotate(coin_balance=F("balance_account__coin_balance"))
            .annotate(rank=Window(expression=RowNumber(), order_by="-coin_balance"))
            .filter(id=data.get("user_id"))
            .first()
        )

Is there a way to avoid the race or freeze the filtered queryset?

qs = set(
      MainUser.objects.select_related("balance_account")
     .annotate(coin_balance=F("balance_account__coin_balance"))  
     .annotate(rank=Window(expression=RowNumber(), 
      order_by="-coin_balance"))
     )
q = list(filter(lambda x: x.id == data.get("user_id"), qs))[0]

But how optimal is this approach?

Upvotes: 0

Views: 51

Answers (1)

Serhii Fomenko
Serhii Fomenko

Reputation: 1030

Your option - .filter(id=data.get(“user_id”)) doesn't work because the django ORM applies filtering to the internal query, so the database will filter by id before it calculates rank. Because of this, you get rank=1 as a result. The python filtering option can be used, especially if you have few records, but it will be slowly if you have a large number of records, it is much faster to do it through the database.

You can try using this option, it should give you the expected results. We will simply get the user ID through another window function FirstValue, and then we can perform filtering using the obtained value.

from django.db.models.functions import FirstValue, RowNumber

result = (  
    MainUser.objects  
    .select_related('balance_account')  
    .alias(coin_balance=models.F('balance_account__coin_balance'))  
    .annotate(  
        rank=models.Window(expression=RowNumber(), order_by='-coin_balance'),  
        user_id=models.Window(FirstValue('id'), partition_by='id'),  
    )  
    .filter(user_id=data.get("user_id"))  
    .first()  
)

p.s. Yes, I know the question is pretty old by now, but maybe this answer will help someone else.

Upvotes: 1

Related Questions