如何对bookings集合做聚合查询以查找指定日期范围内的可用房间

核心判断逻辑 #

• 可用房间需同时满足两个条件：
  • 房间本身处于启用状态（rooms.status: true，若业务规则相反可自行调整判断值）
  • 查询时段内不存在有效重叠预订
• 预订时间重叠判定：有效预订的时间范围与查询时间范围存在交集，判断规则为 booking.from < 查询结束时间 AND booking.to > 查询开始时间

完整聚合语句 #

你可以直接在 rooms 集合上执行如下聚合操作：

// 先定义查询的时间边界
const QUERY_START = ISODate("2021-10-01T00:00:00.000Z");
const QUERY_END = ISODate("2021-10-31T23:59:59.999Z");

db.rooms.aggregate([
  // 第一步：先过滤掉未启用的房间，减少后续计算量
  {
    $match: {
      status: true
    }
  },
  // 第二步：关联查询当前房间在指定时段内的有效重叠预订
  {
    $lookup: {
      from: "bookings",
      let: { roomId: "$_id" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                // 匹配对应房间
                { $eq: ["$room", "$$roomId"] },
                // 只取有效预订，若业务中status为false代表有效可自行修改
                { $eq: ["$status", true] },
                // 判定预订和查询时段重叠
                { $lt: ["$from", QUERY_END] },
                { $gt: ["$to", QUERY_START] }
              ]
            }
          }
        }
      ],
      as: "overlap_bookings"
    }
  },
  // 第三步：过滤出没有重叠预订的房间，即为可用房间
  {
    $match: {
      overlap_bookings: { $size: 0 }
    }
  },
  // 可选：移除不需要的overlap_bookings字段，精简返回结果
  {
    $unset: "overlap_bookings"
  }
])

优化建议 #

如果数据量较大，可以给 bookings 集合建联合索引 {room: 1, status: 1, from: 1, to: 1}，大幅提升关联查询的性能。

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