MongoDB +mongoose join and in query advanced

Question

My DB Schema is complicate and I want to make my current query better

In my users collection I have an array groups that has the groups that this user related to. when the user loggedin I look for getting all the tasks that:

1. task is active
2. current date is between task.fromDate and task.toDate
3. task isin the group that related to current user
4. the group.active is true

foreach task that founded I want get the related actions (task) and related responses(user + task).

my DB schema is here https://mongoplayground.net/p/X9iAEzwDEWa

my current code is running multi queries and I want to make this query better this is my current code

 const {groups} = await User.findOne({ username, active:true })
    .select("groups")
    .lean()
    .exec();
   const tasksQuery = {
    active: true,
    group: { $in: groups },
    $or: [
      {
        fromDate: {
          $exists: false,
        },
        toDate: {
          $exists: false,
        },
      },
      {
        $expr: {
          $and: [
            {
              $lte: ["$fromDate", "$$NOW"],
            },
            {
              $gte: ["$toDate", "$$NOW"],
            },
          ],
        },
      },
    ],
  };
 const tasks =  await Task.find(tasksQuery).lean()

  const tasksWithGroup = await Promise.all(
    tasks.map(async (task) => {
      const group = await Group.findById(task.group).lean().exec();
      const actions =  await Action.find({task:task._id, active:true}).select("_id").lean().exec();
      const numActions = actions?.length
      let doneActions = 0
      let gradeActions=0
      let responses=[]
      //get data for student
      if(numActions && req.isStudent){
        responses = await Response.find({username:req.user ,action:{ $in: actions } }).select(["_id","grade"]).lean().exec()
        if(responses.length) {
          doneActions = responses.length
          gradeActions = responses.filter(c=>c.grade > -1).length
        }
      }
      return { ...task, groupname: group?.name , numActions, actions,doneActions ,gradeActions, responses};
    })
  );

Answer 1

There are several issues with your scenarios.

1. scattered collections: MongoDB is not a relational database. $lookup/joining collections can be expensive. You might want to refactor your schema to denormalize and put records that are frequently accessed together in the same collection.
2. You are relying on application level filtering: you should leverage DB level filtering when possible
3. You are firing multiple db calls to get data: You can use $lookup to get the data you want in an aggregation pipeline in one single db call.

Without diving deep into schema refactoring, which needs much more context on your actual scenario and will not fit in the focused requirement of a single stackoverflow question, here is a tweaked version of your query:

db.users.aggregate([
  {
    "$match": {
      "_id": {
        "$oid": "6390a187bd6b97a4dc58263d"
      }
    }
  },
  {
    "$lookup": {
      "from": "groups",
      "localField": "groups",
      "foreignField": "_id",
      "pipeline": [
        {
          $match: {
            "active": true
          }
        }
      ],
      "as": "groups"
    }
  },
  {
    "$unwind": "$groups"
  },
  {
    "$lookup": {
      "from": "tasks",
      "localField": "groups._id",
      "foreignField": "group",
      "pipeline": [
        {
          $match: {
            $expr: {
              $and: [
                {
                  active: true
                },
                {
                  $gte: [
                    "$$NOW",
                    "$fromDate"
                  ]
                },
                {
                  $lte: [
                    "$$NOW",
                    "$toDate"
                  ]
                }
              ]
            }
          }
        }
      ],
      "as": "tasks"
    }
  },
  {
    "$unwind": "$tasks"
  },
  {
    "$lookup": {
      "from": "responses",
      "localField": "tasks._id",
      "foreignField": "task",
      "pipeline": [],
      "as": "responses"
    }
  },
  {
    "$unwind": {
      path: "$responses",
      preserveNullAndEmptyArrays: true
    }
  }
])

Mongo Playground

The idea is to link up the collections in $lookup and rely on the sub-pipeline to perform the filtering.