Mongoose - How to return counts of two different things?

Question

I am very new to MongoDB and Mongoose and seem to have found myself needing a fairly complex query right off the bat. My schema is below.

const gameSchema = new mongoose.Schema({
    _winnerId: {
        type: mongoose.Schema.Types.ObjectId,
        required: true,
        ref: 'User',
    },
    _loserId: {
        type: mongoose.Schema.Types.ObjectId,
        required: true,
        ref: 'User',
    },
    winnerWords: [String],
    loserWords: [String],
});

I am trying to get the win/loss ratio for a given user id. Somehow, I need to count up both how many times the userid appears as the _winnerId and how many times the userid appears as the _loserId. Can this be done with a single query?

score 0 · Accepted Answer · answered Aug 10 '20 at 22:59

0

It's likely more performant to just use two simultaneous queries, since it such a simple calculation and the winner/loser fields are easily indexed. However, I was curious how this could be accomplished. It can be done like this: https://mongoplayground.net/p/JbBCzyln9o6

db.collection.aggregate([
  // Combine the user _ids
  {$group: {
      _id: ["$_loserId", "$_winnerId"],
      // Track the amount of times user X lost to user Y
      times: {$sum: 1}
  }},
  
  // Seperate the user _ids
  // Track whether the user won by setting field 'lostWon': 0 if they lost 1 if they won
  {$unwind: {
      path: "$_id",
      includeArrayIndex: "lostWon"
  }},

  // Create a group for each user _id, user the 'lostWon' field to sum the totals
  {$group: {
    _id: "$_id",
    lost: {
      $sum: {
        $multiply: [
          "$times",
          {$cond: [{$eq: ["$lostWon", 0]}, 1, 0]}  // Important: swaps the values
        ]
      }
    },
    won: {
      $sum: {
        $multiply: [
          "$times",
          {$cond: [{$eq: ["$lostWon", 1]}, 1, 0]}  // This line is reduntant, but make query more understandable
        ]
      }
    },
  }},

  // Divide the ratio, if $lost is 0 use the won field value to avoid infinity
  {$addFields: {
    ratio: {
      $cond: [
        {$eq: ["$lost",0]},
        "$won",
        {$divide: ["$won", "$lost"]}
      ]
    }
  }}
])

answered Aug 10 '20 at 22:59

GitGitBoom

1,822
4
5

I've added a callback to get the result, however all I get is an empty array. What could possibly be wrong? – Genoe Aug 10 '20 at 23:49
Could be a lot of things. Post your query to the question if possible, also make sure your swapping out ```collection``` with your desired collection. I really do recommend using ```Model.find({_winnerId: userId}).count()```. This is more a proof of concept, it will slow the more documents you have. – GitGitBoom Aug 11 '20 at 00:10
Yup, I am using modelname.aggregate. Now, that said, what you are suggesting is what I have originally been trying. However I get 0 returned every time, so thought maybe I was on the wrong track anyways. I am doing this `const numWins = await db.Game.find({ _winnerId: userId }).count();` and getting 0. userId is a string. I've tried using mongoose.Types.ObjectId(userId) with no effect. – Genoe Aug 11 '20 at 00:21
You will need mongoose.Types.ObjectId. I should have included it. Mongoose automatically will change _id fields but not others. Are you certain you are connected to the correct db? – GitGitBoom Aug 11 '20 at 00:36
If you are querying via mongoose just use the model ```Game.find()``` if you are querying via shell it's probably under ```db.Games.find()``` or ```db.games.find``` depending on your collection name. – GitGitBoom Aug 11 '20 at 00:42
Last thing: if you are querying via shell for testing, be sure to switch to your db via ```use dbname``` before running queries. Otherwise you will get 0 for all counts. – GitGitBoom Aug 11 '20 at 00:48
Update: Figured out why I am getting 0. So, a few days ago I renamed the collection games to game. I used `db.games.renameCollection('game')` in the terminal. However when I save a game a "games" table is created. I've refactored code. I now have `mongoose.model('game', gameSchema);` so I don't know what is going on. – Genoe Aug 11 '20 at 00:48
That will do it. You can provide your collectionName to fix your model then. https://stackoverflow.com/a/7527515/13885615 Monogoose is going to pluralize it otherwise. – GitGitBoom Aug 11 '20 at 00:50
That fixed it. Your recommended query (what I've been trying) now works. However, in case you want to know, the complex single query provided as the answer doesn't seem to be working. My test database has 10 games stored and this is my result `{ _id: '$_winnerId', lost: 0, won: 10, ratio: 10 }, { _id: '$_loserId', lost: 10, won: 0, ratio: 0 }` – Genoe Aug 11 '20 at 01:23
I'd have to see your sample data but that sounds right. Winner won 10 and loser lost 10. Either way 2 requests will be much more efficient at scale. Take a look at ```Promise.all``` so you can execute them simultaneously. – GitGitBoom Aug 11 '20 at 02:16

Mongoose - How to return counts of two different things?

1 Answers1