Leaderboard implementation using Django

Question

I have a model for every user on my site (UserProfile) each profile contains a field named points.

I would like to get the -5 +5 users from the current user while sorting by points. How can I accomplish this ?

Answer 1

You could make two queries, one for the users before the current users, and one for the users just after:

id = current_user.pk
points = current_user.profile.points

before = User.objects.filter(
    Q(profile__points__gt=points) | 
    Q(profile__points=points, pk__lt=id)
).order_by('-profile__points')[:5]

after = User.objects.filter(
    Q(profile__points__lt=points) | 
    Q(profile__points=points, pk__gt=id)
).order_by('profile__points')[:5]

This is bases on two queries:

• All users with a score greater than the current user, or with the same score but with a lower pk.
• All users with a score lower than the current user, or with the same score but with a greater pk.

Then with the proper ordering and limits you can get your result. Of course pk can be replaced by any other filed, or just removed altogether. In the latter case, you can instead consider that the current user is always first (this is just an example), and the queries become:

before = User.objects.filter(
    profile__points__gt=points,
).order_by('-profile__points')[:5]

after = User.objects.filter(
    profile__points__lte=points,
).exclude(pk=id).order_by('profile__points')[:5]

Alternatively, to get just the index of the current user in the list of users sorted by points, you could do:

id = current_user.pk
points = current_user.profile.points
index = User.objects.filter(
    Q(profile__points__gt=points) |
    Q(profile__points=points, pk__lt=id)
).count()

Then your list of users centered on the current one will just be:

User.objects.all().order_by('-profile__points', 'pk')[index - 5:index + 6]

This alternative could be slower if you have a lot of users, as the whole list of users before the current one needs to be evaluated, but I did not verify this.

Answer 2

I'm unsure what your points are but this should just be annotate, something like below...

from django.db.models import Sum, Avg

UserProfile.objects.annotate(sum_rating=Sum('sum__points')).order_by('-sum_points')

Don't forget you can use the annotation variable in a filter.

Update: just order by points, note the -

UserProfile.objects.filter(whatever here).order_by('-points')[:5]

or

.order_by('points')[:5]