django: grouping in an order_by query?

I want to allocate rankings to users, based on a points field.

Easy enough you'd think with an order_by query. But how do I deal with the situation where two users have the same number of points and need to share the same ranking? Should I use annotate to find users with the same number of points?

My current code, and a pseudocode description of what I'd like to do, are below.

    top_users = User.objects.filter(problem_user=False).order_by('-points_total')
        # Wrong - in pseudocode, this should be 
        # Get the highest points_total, find all the users with that points_total,
        # if there is more t开发者_开发百科han one user, set status to 'Joint first prize',
        # otherwise set status to 'First prize'
    top_users[0].status = "First prize"
    if (top_users[1]): 
            top_users[1].status = "Second prize"
    if (top_users[2]): 
            top_users[2].status = "Third prize"
    if (top_users[3]):
            top_users[3:].status = "Highly commended"

The code above doesn't deal with the situation where two users have the same number of points and need to share second prize. I guess I need to create a query that looks for unique values of points_total, and does some kind of nested ranking?

It also doesn't cope with the fact that sometimes there are fewer than 4 users - does anyone know how I can do (in pseudocode) 'if top_users[1] is not null...' in Python?

I'd just use itertools.groupby. Something like:

top_users = [(k, list(g)) for k,g in groupby(top_users, key=lambda x: x.score))]
for u in top_users[0][1]:
    u.status = 'First prize'
for u in top_users[1][1]:
    u.status = 'Second prize'
for u in top_users[2][1]:
    u.status = 'Third prize'
for score, users in top_users[3:]:
    for u in users:
        u.status = 'Highly recommended'

Or even better, use itertools.count instead of the 4 loops:

top_users = [(k, list(g)) for k,g in groupby(top_users, key=lambda x: x.score))]
for c, (score, group) in zip(count(0), top_users):
    if c == 0:
        prize = 'First prize'
    elif c == 1:
        prize = 'Second prize'
    elif c == 2:
        prize = 'Third prize'
    else:
        prize = 'Highly recommended'
    map(lambda x: setattr(x, 'status', prize), group)

And the last refinement, maybe keep a prize list instead of the if statements.

top_users = [(k, list(g)) for k,g in groupby(top_users, key=lambda x: x.score))]
prize_list = ['First prize', 'Second prize', 'Third prize', 'Highly recommended']
for c, (score, group) in zip(count(0), top_users):
    prize = prize_list[c] if c < len(prize_list) else prize_list[-1]
    map(lambda x: setattr(x, 'status', prize), group)

The caveat of this approach is that you're not doing the grouping in the database, but instead you'd be doing it in memory. This may be a problem if there are a lot of users. See How to query as GROUP BY in django? for some guidance on how to do this in the database.

Quick & untested code, hopefully you get the idea:

top_users = User.objects.filter(...)
prizes = ['First prize', 'Second', 'Third', ...]
prize = 0
previous_points = None
try:
    for user in top_users:
        if user.points_total < previous_points:
            # always skipped in first iteration
            prize += 1
        user.status = prizes[prize] # raise IndexError when out of prizes
        previous_points = user.points_total
except IndexError:
    pass

More elegant solutions welcomed!