Find ids of free rooms in a specified date or date range

I am building a Hotel Booking App. Bookings are subset of Rooms.

What I am trying to achieve → Find no of rooms free on a specified date with corresponding ids.

My query is able to find free rooms in a date range, but fails to include the date in between the date range.

What am I getting wrong? I am fairly new to MongoDb. Please pardon me if this is very basic question.

The query is

    {
    ‘$match’: {
      ‘bookings’: {
        ‘$not’: {
          ‘$elemMatch’: {
              ‘from’: {
                 ‘$gte’: new Date(fromDate)
                 },
              ‘to’: {
                ‘$lte’: new Date(toDate)
                 }
              }
           }
         }
      }
    }

I do not know if I understand your issue correctly but if you are looking for bookings within the date range then may be you should remove $not.

Please let me elaborate what I am trying to do. Pardon me, I did not enclose the complete query. This is the complete aggregation query:-

     {
      '$match': {
        'bookings.from': {
          '$ne': new Date(fromDate)
        }
      }
    }, {
      '$match': {
        'bookings.to': {
          '$ne': new Date(fromDate)
        }
      }
    }, {
      '$match': {
        'bookings': {
          '$not': {
            '$elemMatch': {
              'from': {
                '$gte': new Date(fromDate)
              }, 
              'to': {
                '$lte': new Date(toDate)
              }
            }
          }
        }
      }
    }

Rooms collection contains list of all rooms available in a Hotel.
Bookings collection contains list of bookings for a specific room id.

Expected result → Based on the bookings I want to find free rooms on a specific date or a date range.
the above mentioned query looks in to bookings and finds out booked rooms on a specific date range. By negating the result of booked rooms I retrieve free rooms. Say if I have only one room and the room is booked from 12 Aug to 20 Aug, if this query is executed to find out room available between 15 Aug to 17 Aug, the expected result is 0. But, this query returns the result as room is available.

After searching the entire day i have understood that I have done a simple comparison. But, still I have no clue how to achieve the desired result. Thanks.

It would help us to experiment if you could share some documents in your collection.

The following is a single room in a hotel. Each hotel is identified by hotelName which is a unique name (Not using _id as per requirements). Please find the example HotelRoom collection below:-
> {

       "_id"::"610faf68672b01a5f6518c34",
       "roomName":"Lion's Den",
       "rent":800,
       "hotelName":"Sarvashresth",
       "createdAt" : "2021-08-08T10:18:16.823Z
       "bookings" : [
         {
            "checkedout": false,
            "_id": "611157cec3e515b600f5c8a7",
            "name": "John Doe",
            "from": "2021-08-07T18:30:00.000Z",
            "to": "2021-08-17T18:30:00.000Z",
            "room": "610faf68672b01a5f6518c34",
            "createdAt": "2021-08-09T16:29:02.726Z",
            "updatedAt": "2021-08-09T16:29:02.727Z",
            "__v": 0
           }
        ]
       "__v":0
     }

Hi @Pradeep_M ,

I think the following query should cover your search:

db.hotels.find({
  bookings : {$elemMatch : {
      $or: [
        { from: { $gte: ISODate(<to_date>)} },
        { to: { $lte: ISODate(<from_date>) }}
    ]

  }}
})

The idea here is that we avoid any intersection of the from and to searched dates. The only mandatory request from the application is to make sure that the searched “from date” is always before the searched “to date” which makes sense any way.

Your example worked for me:

db.hotels.find({
  bookings : {$elemMatch : {

    $or: [
        { from: { $gte: ISODate('2021-08-29')} },
        { to: { $lte: ISODate('2021-08-18') }}
    ]

  }}
})
{ _id: '610faf68672b01a5f6518c34',
  roomName: 'Lion\'s Den',
  rent: 800,
  hotelName: 'Sarvashresth',
  createdAt: 2021-08-08T10:18:16.823Z,
  bookings: 
   [ { checkedout: false,
       _id: '611157cec3e515b600f5c8a7',
       name: 'John Doe',
       from: 2021-08-07T18:30:00.000Z,
       to: 2021-08-17T18:30:00.000Z,
       room: '610faf68672b01a5f6518c34',
       createdAt: 2021-08-09T16:29:02.726Z,
       updatedAt: 2021-08-09T16:29:02.727Z } ] }
db.hotels.find({
  bookings : {$elemMatch : {
   

    $or: [
        { from: { $gte: ISODate('2021-08-29')} },
        { to: { $lte: ISODate('2021-08-01') }}
    ]

  }}
})
test>

Thanks
Pavel

Ok This might not work with several bookings in a single document as thee first booking will not intersect and the document will be returned.

I think the best way is to query the relevant hotels and rooms and for each room query if there are booking dates between those dates and if it returns result remove it from the user view and go to present a room that has 0 results from the “booking” query.

Ok @Pradeep_M Now I think I nailed it:

var from_date=ISODate('2021-08-01')
var to_date=ISODate('2021-08-19')

db.hotels.find({
  $and : [ {bookings : {$elemMatch : {

    $or: [
        { from: { $gte: to_date} },
        { to: { $lte: from_date }}
    ]

  }}},  {bookings : { $not :  {$elemMatch : {
  
    $or: [
        { from: { $gte: from_date, $lte : to_date } },
        { to: { $lte: to_date , $gte :  from_date}}
    ]

  }}  } }]
})

This is a bit complex but I try to cover all possibilities , test it :slight_smile:

1 Like

Thank you so much @Pavel_Duchovny works like a charm. I did a bit of digging and found out an alternative. I know that it is not efficient but can you please comment on it? I have modified the schema of Bookings to add a date range → the range of dates the room is booked.
When querying I use the date range to find out the rooms that aren’t booked in the range.
Works fine. Is it ok to do this way?

 {
         "_id":"610faf68672b01a5f6518c34",
         "name":"Delhi",
        "rent":800,
         "hotelName":"The Inn",
         "createdAt":{"$date":"2021-08-08T10:18:16.823Z"},
        "__v":0,
        "bookings":[
               {
               "checkedout":false,
              "dateRange":[
                      "2021-08-17T00:00:00.000Z"
               "_id":"6113a01e7736aec46f502635",
               "name":"John",
               "email":"",
               "phone":null,
               "from":{"$date":"2021-08-17T00:00:00.000Z"},
               "to":{"$date":"2021-08-17T00:00:00.000Z"},
               "room":"610faf68672b01a5f6518c34",
               "createdAt":"2021-08-11T10:02:06.143Z",
               "updatedAt":"2021-08-11T10:02:06.143Z",
               "__v":0}
        ]
 }

Not sure what you try now.

Why my approach is not acceptable, considering that you add a specific hotel or area criteria to a geo or regular index the search should be ok…

I have used your solution in production now. The solution I provided above, is the one that I came up, after doing some research, before you gave me a solution.
I want to understand the pros and cons of the solution that I came up with.

Allow me to explain what modifications I have done.

  1. I added an extra field → Date Range - contains the dates between the From and To (both inclusive)
  2. My query now is
    > [
       {
                '$match': {
                  'bookings.dateRange': {
                    '$nin': [
                      ...dateList // This date list will be similar list of dates between from date and to date (both 
                                      //inclusive)
                    ]
                  }
                }
              },
        { 
        '$project': {
            'bookings': 0
        }
            ]

Please note that your solution works well for all use cases. Haven’t encountered a fail case till now. I want to understand the implications of my solution for a better understanding. I have one more solution which is much complex, similar to the one above. If you are interested I will share that one too.

So if a booking is for a month you store 30 days?

$nin is not a selective expression , its up for your testing.

How did you index this collection?

@Pavel_Duchovny as per the requirements the booking can be up to 30 days, max. Yes, I did store 30 days in the date range field.
I did not understand what you mean by a selective expression.
I haven’t created any indexes so far. Should I?

As far as now your snippet is in production. But I would like to assess the solution that I came up with.
Thanks :grinning:.

For selectivity read :

Now you should definitely index your filter fields.

Read

@Pavel_Duchovny thank you. Got what I wanted. Cheers. :beers: