1

Let us assume, I have an AirBnb type app. Where users can book houses to stay. Now, I have a database schema for this app, when I find houses for a query, I need to find houses that are available in between 2 dates(given by the user). So that it is not clashing with another booking.

Now, we already have a booking from 18Aug - 20Aug(Below code). If the user is searching for houses and the start/end date is 18/19/20Aug, this property should not be displayed.

Is there anything like NOT BETWEEN in MongoDB, or a workaround so that I can filter the results properly.

{  // This is a snippet of the complete document

    "etc": "etc etc etc",
    "transaction": 
        {
            "start": "2018-08-18T21:30:00+05:30",  // datetime object
            "end": "2018-08-20T21:30:00+05:30"  // datetime object
        },
    "etc2": "etc2 etc2"
}

Or If I can design the schema a bit differently?

yugantar
  • 1,970
  • 1
  • 11
  • 17
  • One way to do this is to look for existing reservations that clash with the requested reservations and then subtract from the set of houses. This will handle the case as well where there are no existing reservations for a house. To find overlapping reservations you would need a comparison like: requested_start < reservation_end && requested_end > reservation_start. – James Wahlin Aug 17 '18 at 12:54
  • This will probably take a lot of time query. I can try it with the overlapping solution. – yugantar Aug 17 '18 at 13:07
  • Please try this referring following thread. Mongo has $gte and $lt operator . https://stackoverflow.com/questions/19819870/date-query-with-isodate-in-mongodb-doesnt-seem-to-work – Tanmay Patil Aug 17 '18 at 13:27
  • Thanks for sharing, but, I was looking for a more complex query which can find something like this -> either both start & end dates should be before this transaction, or after this transaction, and also not fall in between other transaction, like 23Aug-25Aug, because there could be other bookings for the same apartment. – yugantar Aug 17 '18 at 21:29
  • Hi @yugantar, have you finally found a solution? :) I have a similar task, to show available houses on the map by the given time frame. I would really appreciate if you can share db schema design. – Bullet-tooth Dec 08 '19 at 20:38
  • 1
    Hi @Bullet-tooth, It's been more than a year I asked this question so I don't remember exactly how I did it at that time but I have written a snippet for you now. Let me know if it helps. https://codeshare.io/29qYRg – yugantar Dec 09 '19 at 11:07

0 Answers0