django: grouping in an order_by query?
- by AP257
Hi all,
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 than 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?
