CODEWITHSUNDEEP
node.jsmongodbexpressmongoose
Kirubel
Kirubel

Reputation: 1627

MongoDb conditional grouping

I have two fields for a vote poll like vote1 and vote2 which hold a candidateId. What I was trying to do is, calculate the number of vote counts for each candidate in my VotePoll collection.

My VotePollSchema looks like the following:

VotePollSchema = mongoose.Schema({
    user: {
      type: mongoose.Schema.Types.ObjectId,
      ref: "User",
      required: true,
    },
    vote1: {
      desc: "First Voted Candidate",
      type: mongoose.Schema.Types.ObjectId,
      ref: "Candidate",
      type: String,
    },
    vote2: {
      desc: "Second Voted Candidate",
      type: mongoose.Schema.Types.ObjectId,
      ref: "Candidate",
      type: String,
    },
    isActive: {
      desc: "is Active.",
      type: Boolean,
      default: true,
      required: true,
    },
});

What I want to achieve here is that count the number of votes for a candidate which might be in vote1 or vote2. Since each User is able to vote for one or two candidates at the same time.

So, how should I group this collection by the candidate? Which could be either in vote1 or in vote2. I've tried something like this but I don't think it's the appropriate way of doing it.

 const votePolls = await VotePoll.aggregate([
      { $match: { isActive: true } },
      {
        $group: {
          _id: {
            $or: [{ firstVotedCandidate: "$firstVotedCandidate" }],
          },
          voteCount: { $sum: 1 },
        },
      },
 ]);

My expected result:

{
   candidateOne: {
      candidateName: "Joshua Solomon"
      voteCount: 140
   }
   candidateTwo: {
      candidateName: "Jenny Doe"
      voteCount: 25
   }
   ...
}

Sample input data:

[
  {
    "isActive": true,
    "user": {
      "fullName": "Joshua Solomon",
      "createdAt": "2021-08-02T13:33:14.584Z",
      "updatedAt": "2021-08-02T13:36:35.041Z",
      "registeredBy": "61026c5e4fd8a53c98b8c579",
      "id": "6107f4182e21a42bd8b2b9cb"
    },
    "vote1": {
      "isActive": true,
      "fullName": "Candidate 1",
      "title": "Available Candidate",
      "createdAt": "2021-07-28T12:35:27.868Z",
      "updatedAt": "2021-07-28T12:35:27.868Z",
      "id": "61014f0f8d0ad041c0cf493a"
    },
    "vote2": {
      "isActive": true,
      "fullName": "Candidate 4",
      "title": "Fourth Candidate",
      "createdAt": "2021-07-28T12:36:13.229Z",
      "updatedAt": "2021-07-28T12:36:13.229Z",
      "id": "61014f3d8d0ad041c0cf4940"
    },
    
    "createdAt": "2021-08-02T13:36:34.859Z",
    "updatedAt": "2021-08-02T13:36:34.859Z",
    "id": "6107f4e22e21a42bd8b2dae2"
  },
  {
    "isActive": true,
    "user": {
      "fullName": "Jenny Doe",
      "createdAt": "2021-08-02T13:33:15.351Z",
      "updatedAt": "2021-08-03T06:10:53.632Z",
      "registeredBy": "61026c5e4fd8a53c98b8c579",
      "id": "6107f4192e21a42bd8b2d136"
    },
    "vote1": {
      "isActive": true,
      "fullName": "Candidate 4",
      "title": "Fourth Candidate",
      "createdAt": "2021-07-28T12:36:13.229Z",
      "updatedAt": "2021-07-28T12:36:13.229Z",
      "id": "61014f3d8d0ad041c0cf4940"
    },
    "vote2": {
      "isActive": true,
      "fullName": "Candidate 2",
      "title": "Second Candidate",
      "createdAt": "2021-07-28T12:35:56.425Z",
      "updatedAt": "2021-07-28T12:35:56.425Z",
      "id": "61014f2c8d0ad041c0cf493c"
    },
    
    "createdAt": "2021-08-03T06:10:53.389Z",
    "updatedAt": "2021-08-03T06:10:53.389Z",
    "id": "6108ddedcc1c4f2a505b4028"
  }
]

Upvotes: 1

Views: 58

Answers (1)

I.Blair
I.Blair

Reputation: 581

This aggregate will return results in a sorted array:

[
  {
    '$addFields': {
      'votes': [
        '$vote1', '$vote2'
      ]
    }
  }, {
    '$unwind': {
      'path': '$votes'
    }
  }, {
    '$group': {
      '_id': {
        'name': '$votes.fullName'
      }, 
      'voteCount': {
        '$sum': 1
      }
    }
  }, {
    '$project': {
      'candidateName': '$_id.name', 
      'voteCount': 1, 
      '_id': 0
    }
  }, {
    '$sort': {
      'voteCount': -1
    }
  }
]

The $addfields the vote fields into an array, The $unwind separates the documents based on the votes, The $group gets the voteCount, it will get the total votes regardless of whether they were the first or second vote The $project is just to clean things up And the $sort is to sort the array from most votes to least.

I think you'd be best suited by changing your schema, vote1 and vote2 store practically the same information so you could simplify by having a schema where you store votes as an array of objects:

VotePollSchema = mongoose.Schema({
user: {
  type: mongoose.Schema.Types.ObjectId,
  ref: "User",
  required: true,
},
votes: [{
  desc: String,
  type: mongoose.Schema.Types.ObjectId,
  ref: "Candidate",
  type: String,
}],
isActive: {
  desc: "is Active.",
  type: Boolean,
  default: true,
  required: true,
},
});

Then you could avoid the $addFields at the start

Upvotes: 2

Related Questions