Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

shahzadsarwar's avatar

Query scopes for reservation system with multiple rooms.

I am working on a hotel reservation system, where user|admin can select multiple rooms. I am facing a problem in the edit functionality. I am looking for help and suggestions on how to make this right.

My schema.

Rooms:

  1. room_number
  2. price
  3. reserved(Boolean)

Reservations:

  1. customer_id
  2. checkin
  3. checkout

Reservation_Room

  1. room_id
  2. reservation_id
  3. TotalPrice

Query scope on rooms

public function scopeIsNotReserved($query) { return $query->where('reserved', false); }

The actual problem is.

  1. I cannot use IsNotReserved query scope while editing a reservation, because it won't pass the value to the selected options.
  2. I would like to pass all the rooms that are not reserved except this reservation.id(See $roomId)

My controller edit function looks like this.

$reservation = Reservation::findOrFail($id);

$customers = Customer::pluck('name', 'id')->toArray();

// Currently not doing anything with this query.

$roomId = $reservation->rooms()->pluck('rooms.id')->toArray();

$rooms = Room::IsNotOnMaintainance()->get();

0 likes
2 replies
lostdreamer_nl's avatar
Level 53

you seem to have a bigger problem.

Rigth now, when I reserve room 1 for 1 week, somewhere next year, that room will be 'reserved' starting now.

So next month, it wont show in the reservation table (even though the room would be free)

You should not have a field 'reserved' on the room table, as it will not tell you WHEN this room is reserved.

Your isNotReserved scope should get 2 more parameters: start / end, and should return rooms that are not reserved within that date range (checking the reservations & reservation_room table).

You could than add an optional third parameter ($ignoreId) which would be returned even if it had a reservation within that time frame (so you can add the current room's ID to that scope to make sure the edit screen can have the current room selected).

2 likes
shahzadsarwar's avatar

Thank you so much for identifying that bigger problem. I will look into it and will try to solve it.

Please or to participate in this conversation.