Find rank of a player based on 2 fields in a collection in mongoose-CodePudding

I have more than 100,000 documents in a collection and I want to get rank of player based on his totalCoins field first and in case 2 players have same coins, then based on their level.

Below is Player collection example:

const players = [
{
   _id: 1,
   name: "abc",
   totalCoins: 100,
   level: 1
},
{
   _id: 2,
   name: "bcd",
   totalCoins: 200,
   level: 2
},
{
   _id: 3,
   name: "cde",
   totalCoins: 300,
   level: 3
},
{
   _id: 4,
   name: "def",
   totalCoins: 100,
   level: 4
},
{
   _id: 5,
   name: "efg",
   totalCoins: 100,
   level: 4
}
]

Let's say I am finding rank of player with _id = 4.

How can i achieve this in mongoose.

Thanks in advance.

CodePudding user response：

collection.count({ 
    "$expr": {
        "$gt": [
            { "$add": [ "$totalCoins", {"$divide":["$level",10000] ] },
            100.0004
        ]
    }
})

The idea is to count the number of records with higher coins/level than the user. The idea I put here is to divide the level by maximum level 1 (here the level is max 9999) so you will always end up with lower than 1 decimal. When you add the coins to that you will get 100.0004, coins.level . Then it is just to compare two numbers accordingly.

CodePudding user response：

If you are using MongoDB 5, then $rank method should work (not 100% sure because I am using version 4.4 ).

If you are not using 5.x then this should work

Step 1 - sorted the data based on level first and then totalCoins

Step 2 - pushed the sorted data in an array using group and push

Step 3 - unwinded the data and added the index of document based on sorted array with the help of includeArrayIndex and named it globalRank (name it whatever you want :) )

Step 4 - finally projected the data again and added 1 in globalRank so that the rank starts at 1 instead of 0 (this return the rank like 1.0 or 2.0... if you don't want this then you can avoid $add and handle this in frontend)

Step 5 - $match to find a specific index

db.collection.aggregate([
  {
    $project: {
      _id: 1,
      name: "$name",
      totalCoins: "$totalCoins",
      level: "$level",
    },
  },
  {
    $sort: {
      level: -1,
      totalCoins: -1,
    },
  },
  {
    $group: {
      _id: null,
      arr: {
        $push: {
          _id: "$_id",
          name: "$name",
          totalCoins: "$totalCoins",
          level: "$level",
        },
      },
    },
  },
  {
    $unwind: {
      path: "$arr",
      includeArrayIndex: "globalRank",
    },
  },
  {
    $project: {
      _id: "$arr._id",
      name: "$arr.name",
      level: "$arr.level",
      totalCoins: "$arr.totalCoins",
      globalRank: { $add: ["$globalRank", 1] },
    },
  },
  { $match: { _id: 4 } },
]);