Re: Ranking Queryset

[Chris Lawlor]

If you can use .annotate() to calculate the rank, you can further filter / order the annotation results.

An example:

class Book(models.Model):

    title = models.CharField(max_length=120)

    ...

class Vote(models.Model):

    user = models.ForeignKey('User')

    score = models.IntegerField(choices=((-1, 'Thumbs Down'), (+1, 'Thumbs Up')))

    book = models.ForeignKey('Book', related_name='votes')

To rank the Books by the sum of thumbs up and thumbs down votes, you could do the following:

Book.objects.all().annotate(rank=Sum('votes__score'))

Each object returned by this QuerySet will now have a 'rank' attribute, which is the sum of the Vote scores. You can then filter or order by rank. For example, to retrieve only books with a positive overall rank:

Book.objects.all().annotate(rank=Sum('votes__score')).filter(rank__gt=0).order_by('rank')

Hope that helps.

* Code isn't tested

On Tuesday, 19 June 2012 13:27:21 UTC-4, Peter Ung wrote:

Hi All,

I'm trying to do ranking of a QuerySet efficiently (keeping it a QuerySet so I can keep the filter and order_by functions), but cannot seem to find any other way then to iterate through the QuerySet and tack on a rank. I dont want to add rank to my model.

I know how I can get the values I need through SQL query, but can't seem to translate that into Django:

SET @rank = 0, @prev_val = NULL;
SELECT rank, name, school, points FROM
    (SELECT @rank := IF(@prev_val = points, @rank, @rank+1) AS rank, @prev_val := points, points, CONCAT(users.first_name, ' ', users.last_name) as name, school.name as school
    FROM accounts_collegebrainuserprofile
        JOIN schools_school school ON school_id = school.id
        JOIN auth_user users ON user_id = users.id
    ORDER BY points DESC) as profile
ORDER BY rank DESC

I found that if I did iterate through the QuerySet and tacked on 'rank' manually and then further filtered the results, my 'rank' would disappear. Is there any other way you can think of to add rank to my QuerySet? Is there any way I could do the above query and get a QuerySet with filter and order_by functions still intact? I'm currently using the jQuery DataTables with Django to generate a leaderboard with pagination (which is why I need to preserver filtering and order_by).

Thanks in advance! Sorry if I did not post my question correctly - any help would be much appreciated.

Peter
 

[Peter Ung]

Thanks  Chris for your response; however, its not the solution I'm looking for. I need the ranking to go in sequential order - the solution you provided will just make my points, or scores in your example, the rank. I need, for example, the person with the highest number of scores/points with the rank 1 associated their record in the queryset. Then rank 2 for the next person with highest score/points. etc

Any ideas on how to achieve that? Only solution I'm seeing at the moment is to add rank to the model and update rank every time a user calls the leaderboard - which would be expensive and slow wouldn't it?

[akaariai]

On 20 kesä, 20:37, Peter Ung <ung.pe...@gmail.com> wrote:
> Thanks  Chris for your response; however, its not the solution I'm looking
> for. I need the ranking to go in sequential order - the solution you
> provided will just make my points, or scores in your example, the rank. I
> need, for example, the person with the highest number of scores/points with
> the rank 1 associated their record in the queryset. Then rank 2 for the
> next person with highest score/points. etc
>
> Any ideas on how to achieve that? Only solution I'm seeing at the moment is
> to add rank to the model and update rank every time a user calls the
> leaderboard - which would be expensive and slow wouldn't it?

Django's ORM doesn't support windowing functions. The problem isn't
adding the windowing function (rank() in this case) to the SELECT
clause. You can do this with .extra() currently. The real problem is
that to filter on the result, you need to push the query down into
inner subquery, then do the filter in the outer query. This isn't
supported.

So, it seems this is not doable without using raw SQL.

- Anssi