Get the placement of documents in the collection based on the sort order

Question

I am new to MongoDB (+ Mongoose). I have a collection of records with documents that look like this:

{id: 123, user: 'User14', score: 101} {id: 231, user: 'User10', score: 400} {id: 412, user: 'User90', score: 244} {id: 111, user: 'User12', score: 310} {id: 221, user: 'User88', score: 900} {id: 521, user: 'User13', score: 103} + thousands more... 

now I get the 5 best players:

 highscores .find() .sort({'score': -1}) .limit(5) .exec(function(err, users) { ...code... }); 

which is great, but I would also like to make a query like "What placement does user12 in the highscore list?"

Is there any way to achieve this using a query?

Answer 1

This can be done using mapReduce , but this requires that you have an index in the sorted field, if you have not already done so:

 db.highscores.ensureIndex({ "score": -1 }) 

Then you can do this:

 db.highscores.mapReduce( function() { emit( null, this.user ); }, function(key,values) { return values.indexOf("User12") + 1; }, { "sort": { "score": -1 }, "out": { "inline": 1 } } ) 

Or change this to the information you need to return, and not just to the "ranking" position. But since it basically puts everything in a large array that has already been sorted by account, it probably won't be the best performance for any reasonable data size.

A better solution would be to maintain a separate “ranking” collection, which you can update again with mapReduce, even if it does not reduce:

 db.highscores.mapReduce( function() { ranking++; emit( ranking, this ); }, function() {}, { "sort": { "score": -1 }, "scope": { "ranking": 0 }, "out": { "replace": "rankings" } } ) 

You can then query this collection to get your results:

 db.rankings.find({ "value.user": "User12 }) 

So, this will contain the ranking as “emitted” in the _id field of the “ranking” collection.

Answer 2

If you don’t need to place a place in real time, Neil Lunn’s answer is perfect. But if your application always contains data to be inserted into this collection, for new data you cannot get a place for it.

Here is another solution:

First, you add the index to the field account in this collection. Then use the query db.highscores.count({score:{$gt: user score}) . He will count documents whose score is greater than the target. This room is a placement.