Reputation: 416
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
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