I have 3 models like so: A resort:

let resort = {
   _id: "some id"
}

Room that corresponds to a resort

let room = {
   _id: "some id",
   resort_id: "some resort id", 
   room_status: "Ongoing", //  Ongoing, Available,
   no_of_persons_allowed: 4
}

And booking:

let booking = {
   booking_room_id: "some room id",
   booking_resort_id: "some resort id",
   booking_status: "Booked",// Booked, Ongoing, Completed
   checkIn_date: "some date",
   checkOut_date: "some date"
}

I need to query resort that satisfy the following: Let's say i have a resort of 10 rooms, and each room can take up to 4 persons(in actual case this can vary between rooms), and someone wants to book a room for 6 people and 7 days. So let's assume that 8 rooms are already booked on the given dates. 2 rooms are free, so that resort is valid for the customer. Since he can get a room for 6 person as 2 rooms for 8 persons are free. So the resort is returned.

CodePudding user response：

1. find all rooms which is Available and could have more than 6 people
2. find booking for step 1 room.
3. keep those rooms do not have booking

db.room.aggregate([
  {
    $match: {
      no_of_persons_allowed: {
        $gte: 6
      },
      room_status: "Available"
    }
  },
  {
    $lookup: {
      from: "booking",
      localField: "_id",
      // if room_id is unique even in different resort
      foreignField: "booking_room_id",
      as: "isbooking",
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $gt: [
                    "$checkOut_date",
                    ISODate("2022-10-02T00:00:00.000Z")// customer checkin day
                    
                  ]
                },
                {
                  $lt: [
                    "$checkIn_date",
                    {
                      $dateAdd: {
                        startDate: ISODate("2022-10-02T00:00:00.000Z"),
                        // customer checkin day
                        unit: "day",
                        amount: 7
                      }
                    }
                  ]
                },
                {
                  $ne: [
                    "booking_status",
                    "Completed"
                  ]
                }
              ]
            }
          }
        }
      ]
    }
  },
  {
    $match: {
      isbooking: []
    }
  },
  {
    $lookup: {
      from: "resort",
      localField: "resort_id",
      // if room_id is unique even in different resort
      foreignField: "_id",
      as: "resorts"
    }
  }
])

mongoplayground