如何从MongoDB三个集合筛选非休假且无指定日期事件的员工

要筛选出指定日期既不在休假状态、也未被指派事件的员工，可以通过Mongoose的聚合管道实现，核心思路是先排除处于有效休假的员工，再排除被分配到当日事件的员工。

前提说明 #

• 休假仅考虑status: "approved"的记录（若需包含其他状态，可修改条件）
• 假设events集合的teamList字段存储的是员工的ObjectId数组（若实际存储的是员工姓名，需调整匹配字段为name）
• 设指定查询日期为targetDate（格式为Date类型）

聚合查询代码 #

const targetDate = new Date('2024-05-20'); // 替换为你需要查询的日期

const availableEmployees = await Employee.aggregate([
  // 关联leaves集合，查询当前员工在指定日期的有效休假记录
  {
    $lookup: {
      from: 'leaves',
      localField: '_id',
      foreignField: 'userId',
      pipeline: [
        {
          $match: {
            status: 'approved',
            startDate: { $lte: targetDate },
            endDate: { $gte: targetDate }
          }
        }
      ],
      as: 'activeLeaves'
    }
  },
  // 过滤掉有有效休假的员工（仅保留无休假记录的）
  {
    $match: {
      activeLeaves: { $size: 0 }
    }
  },
  // 关联events集合，查询当前员工被指派到指定日期的事件记录
  {
    $lookup: {
      from: 'events',
      let: { empId: '$_id' },
      pipeline: [
        {
          $match: {
            eventDate: {
              $gte: new Date(targetDate.setHours(0, 0, 0, 0)),
              $lt: new Date(targetDate.setHours(23, 59, 59, 999))
            },
            $expr: {
              $in: ['$$empId', '$teamList'] // 若使用teamMembers字段则替换为$teamMembers
            }
          }
        }
      ],
      as: 'assignedEvents'
    }
  },
  // 过滤掉被指派到当日事件的员工（仅保留未被指派的）
  {
    $match: {
      assignedEvents: { $size: 0 }
    }
  },
  // 投影需要返回的字段（排除敏感字段）
  {
    $project: {
      name: 1,
      role: 1,
      phoneNumber: 1,
      email: 1,
      empType: 1,
      password: 0,
      token: 0
    }
  }
]);

console.log(availableEmployees);

代码解释 #

1. 关联休假记录：通过员工_id匹配休假集合，仅筛选指定日期内已批准的休假。
2. 过滤休假员工：仅保留无有效休假记录的员工。
3. 关联事件记录：匹配指定日期的事件，检查员工是否在指派名单中。
4. 过滤事件员工：仅保留未被指派到当日事件的员工。
5. 字段投影：返回必要的员工信息，避免泄露password、token等敏感字段。

注意事项 #

• 若eventDate包含时间信息，需用范围查询覆盖当日全天，确保不会遗漏事件。
• 若teamList存储的是员工姓名而非ObjectId，需将$expr中的$$empId替换为$name。
• 若需包含pending状态的休假，修改$match中status的条件为{ $in: ['approved', 'pending'] }即可。

内容的提问来源于stack exchange，提问作者Amrit Sidhu