In my DB user can relate to array of groups

I have also collection of task, each task is related to group. And each task can be with from date to date (not required)

And for each task I can have multi Actions

My Query I need

Get all tasks and its actions that

• TASK not limited by time | or | the current date is between the fromDate and the toDate

• TASK related to group that the current user related

• TASK is active

This is my DB structure:

const userSchema = new mongoose.Schema(
  {
  ....
    groups: [
      {
        type: mongoose.Schema.Types.ObjectId,
        ref: "Group",
      },
    ],
    
  }
);

const groupSchema = new mongoose.Schema({
   .....
})

const taskSchema = new mongoose.Schema({
    group:{
        type:mongoose.Schema.Types.ObjectId,
        required:true,
        ref:'Group'
    },
    ......
    fromDate:{
        type:Date,
        required:false
    },
    toDate:{
        type:Date,
        required:false
    },
    active:{
        type:Boolean,
        default:true
    }
})

const actionSchema = new mongoose.Schema({
    task:{
        type:mongoose.Schema.Types.ObjectId,
        required:true,
        ref:'Task'
    },
    ....

})

this is my current test code for review

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

CodePudding user response：

If I understand correctly, you want something like:

db.users.aggregate([
  {$match: {_id: ObjectId("5e41877df4cebbeaebec5173")}},
  {$lookup: {
      from: "tasks",
      localField: "groups",
      foreignField: "group",
      as: "tasks",
      pipeline: [
        {$match: {
            $and: [
              {active: true},
              {$or: [
                  {fromDate: {$exists: false}, toDate: {$exists: false}},
                  {$expr: {$and: [
                        {$lte: ["$fromDate", "$$NOW"]},
                        {$gte: ["$toDate", "$$NOW"]}
                  ]}}
              ]}
            ]
        }}
      ]
  }},
  {$project: {tasks: 1, _id: 0}},
  {$unwind: "$tasks"},
  {$lookup: {
      from: "actions",
      localField: "tasks._id",
      foreignField: "task",
      as: "actions"
  }}
])

See how it works on the playground example