MongoDB: Sorting records by the sum of other collection

Question

I have two collections users and tasks and I want to sort the users by the sum of tasks that they created in tasks collection.

Users collection.

users: [
  { _id: ObjectId('1...'), username: 'jack' },
  { _id: ObjectId('2...'), username: 'john' },
  { _id: ObjectId('3...'), username: 'james' }
]

Tasks collection.

tasks: [
  { _id: ObjectId('...'), userId: '1...', taskName: 'task 1' },
  { _id: ObjectId('...'), userId: '1...', taskName: 'task 2' },
  { _id: ObjectId('...'), userId: '1...', taskName: 'task 3' },
  { _id: ObjectId('...'), userId: '2...', taskName: 'task 1' },
  { _id: ObjectId('...'), userId: '3...', taskName: 'task 1' },
  { _id: ObjectId('...'), userId: '3...', taskName: 'task 2' }
]

This is where I came so far.

UsersModel.aggregate([
  {
    $lookup: {
      from: 'tasks',
      localField: '_id',
      foreignField: 'userId',
      as: 'tasks'
    }
  },
  {
    $group: {
      _id: '$_id',
      totalTasks: { $sum: '$tasks.userId' }
    }
  },
  {
    $sort: { totalTasks: -1 }
  }
], (err, res) => {
  if (err) console.log(err)
  console.log(res)
})

But I'm getting results like this.

[
  { _id: 1..., totalTasks: 0 },
  { _id: 2..., totalTasks: 0 },
  { _id: 3..., totalTasks: 0 }
]

But this is what I'm looking for.

[
  { _id: 1..., totalTasks: 3 },
  { _id: 3..., totalTasks: 2 },
  { _id: 2..., totalTasks: 1 }
]

I'm not sure this is the problem but the type of users._id is ObjectId and the type of tasks.userId is String. I'm stuck and need some help.

Can you convert the `type` of the `tasks.userId` ? What version for mongodb you are on? — Ashh, Jun 30 '19 at 13:39
@Ashh You mean code is correct but it won't work unless I convert the type of `tasks.userId` to `ObjectId`? I'm using `v4.0.3`. — ozgrozer, Jun 30 '19 at 13:43

Tom Slabbaert · Accepted Answer · 2019-06-30T15:14:06.350

2

Easiest way:

  {
    $addFields: {
        temp_id: {$toString: "$_id"}
    }
  },
  {
    $lookup: {
      from: 'tasks',
      localField: 'temp_id',
      foreignField: 'userId',
      as: 'tasks'
    }
  },
  {
    $addFields: {
         totalTasks: {$size: "$tasks"}
     }
  },
  {
    $sort: { totalTasks: -1 }
  }

As long as your scale is not too big this way is fine.

edited Jun 30 '19 at 15:14

answered Jun 30 '19 at 13:50

Tom Slabbaert

21,288
10
30
43

This only works if both of the `users._id` and `tasks.userId` have the same data type which is `ObjectId`. But my `users._id` is `ObjectId` and `tasks.userId` is `String`. So in this case it doesn't work. I think we need a way to convert the data type in this query. – ozgrozer Jun 30 '19 at 14:08
if your on mongo 4.0+ you can cast your objectId to string using [$toString](https://docs.mongodb.com/manual/reference/operator/aggregation/toString/) before the lookup. otherwise you have to do it in code. – Tom Slabbaert Jun 30 '19 at 14:31
Could you give me an example code if that's not too much? I couldn't figure out to implement `$toString` before `$lookup`. – ozgrozer Jun 30 '19 at 15:04
i edited my answer, you just add an $addFields stage. – Tom Slabbaert Jun 30 '19 at 15:14
Thank you so much it's working now. I'm marking as accepted answer. – ozgrozer Jun 30 '19 at 15:26

MongoDB: Sorting records by the sum of other collection

1 Answers1