What should be my MongoDB schema for room booking?

user_11892124 0
Viewed 643

I am building a room booking system in nodejs. Currently I have hotels , rooms and bookings as collections.

rooms is referenced to hotels and bookings is referenced to rooms.

booking.js

const bookingSchema = new mongoose.Schema({
    room: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'rooms'
    },
    start: Date,
    end: Date

});

rooms.js

const roomSchema = new mongoose.Schema({
    roomid: String,
    hotel: {
        type: mongoose.Schema.Types.ObjectId,
        ref: 'hotel_managers'
    },
    type: String,
    price: Number,
    capacity: Number,
    facilities: [String],
    amenities: [String],
    img: [String]

});

hotels.js

const hotel_manager_schema = new mongoose.Schema({
    username: {
        type: String,
        required: true,
        unique: true
    },
    password: {
        type: String,
        required: true
    },
    hotelname: {
        type: String,
        required: true
    },
    role: {
        type: String,
        default: 'manager'
    },
    location: {
        type: String,
        required: true
    },
    img:{
        type: String,
        required: true
    }
})

N.B. This is a service provider ended system, so a hotel is basically a hotel manager with his credentials.

What i want to achieve is when a user sends a query for a given date range, I want to return all the available hotels as well as rooms in a hotel that don't have any booking in the query date range.

I am new in MongoDB so any tips or suggestions on how I can do what I want would be of great help.

node.js mongodb
2 Answers

Here's what you can do according to your schema model architecture; we wanna list all available hotels as well as rooms in hotels that don't have any booking at a given date range.

So to achieve this, we're gonna fetch all bookings that overlaps with date range provided in query and return their room ids; after that we fetch all rooms excluded the array of room ids returned from bookings.

const bookings = await Booking
      .find({
          $or: [
              { start: { $gte: from_date, $lte: to_date } },
              {
                  end: { $gte: from_date, $lte: to_date }
              },
              {
                  $and: [{ start: { $lte: from_date } }, { end: { $gte: to_date } }]
              },
          ],
      })
      .select('room');

Ways in which (a,b) overlaps with (x,y)

const roomIds = bookings.map(b => b.room);

const availableRooms = await Room.find({ _id: { $nin: roomIds } })

You can extract hotel's data by populating Rooms hotel property field:

const availableRooms = await Room
  .find({ _id: { $nin: roomIds } })
  .populate('hotel', 'username password hotelname role location img')

I hope this would work for you.

user_125167670

i am expecting your database is already accumulated with some data and considering that all you have to do is just make a query in your bookingSchema.

    const availableRoom = await Booking.find({ //query today up to tonight
    created_on: {
        $gte: new Date(2012, 7, 14), 
        $lt: new Date(2012, 7, 15)
    }
})

Here Booking is a model. You can find details how to create model over HERE

You can find HERE how to query using dates

user_94579000
Related