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

Deekshith's avatar

condition based query

i have a tables like below,

rooms

id, roomname,capacity
1, Room1, 4

room_slots

id, room_id,from_slot, to_slot
1, 1, 9:00:00, 10:00:00
2, 1, 11:00:00 13:00:00

room_test_bookings

id, room_slot_id, booking_date,test_id

So here requirement is like below in booking page,

  1. select the date.
  2. Display all rooms in select box and user selects any one room
  3. based on room id i should fetch the room_slots
  4. But here i should get slots based on capacity. example: user selected date is: 22nd november 2021 user selects room1 and room 1 capacity is 4 then i should compare this with room_test_bookings . if room_test_bookings has 4 or more entries for a selected room and for selected date then that time slot should be filter out.

i tried code like below,

$roomdata = SubcenterRooms::with(['subcenterroomslots.offlineconvertedtestdetail' => function($q) use($convertbookdate,$gettestid){
            $q->where('student_schedule_date',$convertbookdate)
                ->where('test_id',$gettestid);
        }])->where('room_code',$request->room)->where('active_status',1)->first();

SubcenterRooms.php (Model) (table name: rooms)

public function subcenterroomslots()
    {
      return $this->hasMany('App\RoomTimeSlots','sub_center_room_id','id')->where('active_status',1);
    }

RoomTimeSlots.php (Model) (table name: room_slots)

public function offlineconvertedtestdetail()
    {
        return $this->hasMany('App\OfflineConvertedTest','room_slot_id','id')->where('attended_status','=','None');
    }

Please help me with this how to write a condition inside the query to filter out.

0 likes
3 replies
Sinnbeck's avatar

You say you have a query, but then ask for help? I assume that means it doesn't work? If not, what is wrong with it?

Deekshith's avatar

@Sinnbeck i mean i want to add a condition in the above query to filter out the slot if booked count is greater than seating count.

Please or to participate in this conversation.