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.
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:-
> {
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.
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.
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?
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.
I added an extra field → Date Range - contains the dates between the From and To (both inclusive)
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.
@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 .
.