What should be my MongoDB schema for room booking?-CodePudding

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.

CodePudding user response：

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: [
      {
        from: { $and: [{ $gte: from_date }, { $lte: to_date }] }
      },
      {
        to: { $and: [{ $gte: from_date }, { $lte: to_date }] }
      },
      {
        $and: [{ from: { $gte: from_date } }, { to: { $gte: to_date } }]
      },
    ],
  })

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

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

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

I didn't give it a try, but I hope this would work for you.

CodePudding user response：

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