Is it possible to use queryset in the FROM clause-CodePudding

I have a model for user's points collection:

class Rating(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='rating')
    points = models.IntegerField()

Each user could have multiple records in this model. I need to calculate a rank of each user by sum of collected points. For the listing it's easy:

Rating.objects.values('user__username').annotate(
    total_points=Sum('points')
).order_by('-total_points')

But how to get rank for a single user by his user_id? I added annotation with numbers of rows:

Rating.objects.values('user__username').annotate(
    total_points=Sum('points')
).annotate(
    rank=Window(
        expression=RowNumber(),
        order_by=[F('total_points').desc()]
    )
)

it really added correct ranking numbers, but when I try to get a single user by user_id it returns a row with rank=1. It's because the filter condition goes to the WHERE clause and there is a single row with the number 1. I mean this:

Rating.objects.values('user__username').annotate(
    total_points=Sum('points')
).annotate(
    rank=Window(
        expression=RowNumber(),
        order_by=[F('total_points').desc()]
    )
).filter(user_id=1)

I got the SQL query of this queryset (qs.query) like

SELECT ... FROM rating_rating WHERE ...

and inserted it into another SQL query as "rank_table" and added a condition into the outside WHERE clause:

SELECT * FROM (SELECT ... FROM rating_rating WHERE ...) AS rank_table WHERE user_id = 1;

and executed within the MySQL console. And this works exactly as I need. The question is: how to implement the same using Django ORM?

CodePudding user response：

I have one solution to get what I need. I could add another field to mark records as "correct" or "incorrect" user, sort result by this field and then get the first row:

qs.annotate(
    required_user=Case(
        When(user_id=1, then=1),
        default=0,
        output_field=IntegerField(),
    )
).order_by('-required_user').first()

This works. But SELECT within another SELECT seems more elegant and I would like to know is it possible with Django.

CodePudding user response：

somehow someone just recently asked something about filtering on windows functions. While what you want is basically subquery (select in select), using annotation with the window function is not supported : https://code.djangoproject.com/ticket/28333 because the annotated fields will inside the subquery :'(. One provides raw sql with query_with_params, but it is not really elegant.