How to get item ranking in list sorted by multiple fields in Mongoose

Question

I have a number of user records (> 10000) in a MongoDB collection which can be sorted by score desc + time asc + bonus desc. How can I get the ranking of one user in the list according to this sorting using Mongoose? Assume index has been built correctly.

Answer 1

Count the number of users that come before this user in your sort order. I'll start with the case of a simple (non-compound sort) because the query in the compound case is more complicated, even though the idea is exactly the same.

> db.test.drop()
> for (var i = 0; i < 10; i++) db.test.insert({ "x" : i })
> db.test.find({ }, { "_id" : 0 }).sort({ "x" : -1 }).limit(5)
{ "x" : 9 }
{ "x" : 8 }
{ "x" : 7 }
{ "x" : 6 }
{ "x" : 5 }

For this order, the ranking of a document { "x" : i } is the number of documents { "x" : j } with i < j

> var rank = function(id) {
    var i = db.test.findOne({ "_id" : id }).x
    return db.test.count({ "x" : { "$gt" : i } })
}
> var id = db.test.findOne({ "x" : 5 }).id
> rank(id)
4

The ranking will be based at 0. Similarly, if you want to compute the rank for the document { "x" : i } in the sort { "x" : 1 }, you would count the number of docs { "x" : j } with i > j.

For a compound sort, the same procedure works, but it is trickier to implement because the order in a compound index is lexicographic, i.e., for the sort { "a" : 1, "b" : 1}, (a, b) < (c, d) if a < c or a = c and b < d, so we need a more complicated query to express this condition. Here's an example for a compound index:

> db.test.drop()
> for (var i = 0; i < 3; i++) {
    for (var j = 0; j < 3; j++) {
        db.test.insert({ "x" : i, "y" : j })
    }
}
> db.test.find({}, { "_id" : 0 }).sort({ "x" : 1, "y" : -1 })
{ "x" : 0, "y" : 2 }
{ "x" : 0, "y" : 1 }
{ "x" : 0, "y" : 0 }
{ "x" : 1, "y" : 2 }
{ "x" : 1, "y" : 1 }
{ "x" : 1, "y" : 0 }
{ "x" : 2, "y" : 2 }
{ "x" : 2, "y" : 1 }
{ "x" : 2, "y" : 0 }

To find the rank for the document { "x" : i, "y" : j }, you need to find the number of documents { "x" : a, "y" : b } in the order { "x" : 1, "y" : -1 } such that (i, j) < (a, b). Given the sort specification, this is equivalent to the condition i < a or i = a and j > b:

> var rank = function(id) {
    var doc = db.test.findOne(id)
    var i = doc.x
    var j = doc.y
    return db.test.count({
        "$or" : [
            { "x" : { "$lt" : i } },
            { "x" : i, "y" : { "$gt" : j } }
        ]
    })
}
> id = db.test.findOne({ "x" : 1, "y" : 1 })._id
> rank(id)
4

Finally, in your case of a three-part compound index

{ "score" : -1, "time" : 1, "bonus" : -1 }

the rank function would be

> var rank = function(id) {
    var doc = db.test.findOne(id)
    var score = doc.score
    var time = doc.time
    var bonus = doc.bonus
    return db.test.count({
        "$or" : [
            { "score" : { "$gt" : score } },
            { "score" : score, "time" : { "$lt" : time } },
            { "score" : score, "time" : time, "bonus" : { "$gt" : bonus } }
        ]
    })
}