Rails - how to pull specific sorted data out of database

Question

Hey all,

I have a database with columns (username, points). I am making a call to return some fields from this database to use in a program. I want to pull:

1. The top 10 users with the most points
2. The 5 users above/below the username requesting the rankings

I can pull the top 10 easily enough...

top_users = UserPoints.find(
  :all,
  :conditions => ['points > 0'],
  :order => "points DESC",
  :limit => 10)

Pulling the specific entry for the username requesting is also easy with find_by_username, but how could I determine where this user is ranked? Then, how would you go about finding the 5 users above and 5 users below the specific user (assuming the user is not in the top 10)?

Thanks!

-mark

Answer 1

Maybe do it using two queries?

Users above current user:
UserPoints.all(:conditions => ['points > ?', user.points], :limit => 5, :order => 'points asc')

Users below current user:
UserPoints.all(:conditions => ['points < ?', user.points], :limit => 5, :order => 'points desc')

Maybe there is a way to do it using a single query, I am not an SQL expert, but this should solve it for you.