rank leaderboard in mongo with surrounding players

Question

how would I create a query to get both the current player's rank and the surrounding player ranks. For example, if I had a leaderboard collection with name and points

{name: 'John', pts: 123}

If John was in 23rd place, I would want to show the names of users in the 22nd and 24th place as well.

I could query for a count of leader board items with pts greater than 123 to get John's rank, but how can I efficiently get the one player that is ranked just above and below the current player? Can I get items based on index position alone?

I suppose I can make 2 queries, first to get the number the rank position of a user, then a skip limit query, but that seems inefficient and doesn't seem to have an efficient use of the index

db.leaderboards.find({pts:{$gt:123}}).count();
-> 23

db.leaderboards.find().skip(21).limit(3)

The last query seems to scan across 24 records using the its index, is there a way I can reasonably do this with a range query or something more efficient? I can see this becoming an issue if the user is very low ranked, like 50,000th place.

Answer 1

You'll need to do three queries:

var john = db.players.findOne({name: 'John'})
var next_player = db.players.find(
    {_id: {$ne: john._id}, pts: {$gte: john.pts}}).sort({pts:1,name:1}).limit(-1)[0]
var previous_player = db.players.find(
    {_id: {$ne: john._id}, pts: {$lte: john.pts}}).sort({pts:-1,name:-1}).limit(-1)[0]

Create indexes on name and pts.