[FIXED] How to update a mongodb document depending on values of document referenced by its objectId

Issue

How can I update a MongoDB document depending on values of document referenced by it's objectId? (I am using MongoDB via mongoose)

Let's assume I have two collections. One is called competitions and the other one is called games. A competition can have several games in it. See code example below

// competition documents
[
    {
        compeititionName:"myCompetition",
        games:["617...b16", "617...b19", "617...b1c",
        competitionStatus:"notStarted",
    },
    {
        compeititionName:"yourCompetition",
        games:["617...b18", "617...b19", "617...b1c",
        competitionStatus:"playing",
    },
    {
        compeititionName:"ourCompetition",
        games:["617...b14", "617...b19", "617...b2b",
        competitionStatus:"ended",
    }

]

The competitionStatus above is dependent on the status of the games in that competition.

If all the games have not started then the competition should have notStarted as its competitionStatus. However if any of the games is being played or there are games which have not started and others which are complete then the competition status should be playing. Finally if all the games have ended the the competition status should be ended. An example of how the games collection would look is:

// game documents
[
    {
        _id:"617...b16",
        gameStatus:"notStarted"
    },
    {
        _id:"617...b18",
        gameStatus:"playing"
    },
    {
        _id:"617...b14",
        gameStatus:"ended"
    },
]

How can I update the competitionStatus given the _id of the game whose status has just changed?

Solution

Since it is mongoose, you select the model you want to update first:

const completion = await CompletionModel.FindOne({games: _id_of_the_game});

Then aggregate statuses of all games:

const statuses = await GameModel.aggregate([
  {$match: {_id: {$in: completion.games}}},
  {$group: {_id: gameStatus}}
]).toArray();

Then apply your business logic to set the status:

if(statuses.leength === 1) { // all games have same status
  if(statuses[0]._id === "notStarted") {
    completion.competitionStatus = "notStarted";
  } elseif (statuses[0]._id === "ended") {
    completion.competitionStatus = "ended";
  } else {
    completion.competitionStatus = "playing";
} else {
  completion.competitionStatus = "playing";
}

Then save it to the db:

await completion.save();

Please bear in mind, this pseudo-code is prone to race conditions - if games change status between aggregate() and save() you may end up with stale status in completion documents. You may want to add extra queries to ensure data consistency if required.

UPDATE

If a game can be in more than 1 completion then using Mongoose will be quite inefficient. Starting from v4.2 you can use $merge aggregation stage to do all calculations on the database side, and update matched documents:

db.competition.aggregate([
  {
    $match: {
      games: "id_of_the_game"
    }
  },
  {
    "$lookup": {
      from: "games",
      let: {
        g: "$games"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$g"
              ]
            }
          }
        },
        {
          $group: {
            _id: "$gameStatus"
          }
        }
      ],
      "as": "statuses"
    }
  },
  {
    $set: {
      competitionStatus: {
        "$cond": {
          "if": {
            "$gt": [
              {
                "$size": "$statuses"
              },
              1
            ]
          },
          "then": {
            _id: "playing"
          },
          "else": {
            "$arrayElemAt": [
              "$statuses",
              0
            ]
          }
        }
      }
    }
  },
  {
    "$project": {
      competitionStatus: "$competitionStatus._id"
    }
  },
  {
    "$merge": {
      "into": "competition"
    }
  }
])

Answered By - Alex Blex

Answer Checked By - Willingham (PHPFixing Volunteer)