Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
i have a tables like below,
rooms
id, room_name,
room_time_slots
id, room_id, from_time, to_time
1, 1 , 09:00:00 , 11:00:00
2,1, 12:00:00, 14:00:00
Room.php
public function roomslots()
{
return $this->hasMany('App\RoomTimeSlots','room_id','id')->where('active_status',1);
}
Also now i have a table where admin can block some slots for selected dates like below, blocked_slots
id, room_slot_id, block_date
1, 1, 2022-02-25
2, 2, 2022-02-25
Now i want to fetch all the slots of a particular room and also i want to compare today date with blocked_slots table block_date column to filter blocked slots. i have controller code like below,
$userselectedbookdate= date('Y-m-d',strtotime($bookdate));
$currenttime = currentTime();
$roomdata = Room::with(['roomslots' => function ($q) use($userselectedbookdate,$currenttime){
if($convertbookdate == todaydate())
{
$q->where('from_time','>',$currenttime);
}
}])->where('room_code',$request->room)->where('active_status',1)->first();
How to alter above code to check if room slots present in blocked_slots for $userselectedbookdate?
Thank you.
I don't think you'll be able to compare because in the room_time_slots you are storing only time, while in the blocked_slots you are blocking a whole day based on date. How do you know for which day the room was booked on with your room_time_slots table?
@Nakov Thank you for the reply.
user selects the date in form.
in blocked_slots table i am storing date and slot_id
so first i have to fetch all slot_id if user selected date = block_date
Example:
$bockedslots = DB::table('blocked_slots')->where('block_date',$userselectedate)->pluck('slot_id');
Now in controller i have altered like below,
$roomdata = Room::with(['roomslots' => function ($q) use($userselectedbookdate,$currenttime,$bockedslots ){
$q->whereNotIn('id',$bockedslots )
if($convertbookdate == todaydate())
{
->where('from_time','>',$currenttime);
}
}])->where('room_code',$request->room)->where('active_status',1)->first();
So if you add this in your RoomTimeSlots:
public function blockedSlots()
{
return $this->hasMany(BlockedSlot::class, 'room_slot_id');
}
and then
Room::with(['roomslots' => function ($q) use ($userselectedbookdate, $currenttime) {
$q->where('from_time', '>=', $currenttime)
->whereDoesntHave('blockedSlots', function($query) use ($userselectedbookdate) {
$query->whereDate('block_date', $userselectedbookdate);
});
}])
->where('room_code', $request->room)
->where('active_status', 1)
->first();
Does this return correct result? And what is the type of your from_time table? if that's a string I don't think that the condition above will work for it. You might need to do some database casting.
@Nakov Thank you i am checking this.
from_time is in time format and it will work as i am using this to filter already passed time if booking date is today date so. will update once i get some result.
@Nakov Perfect thank you it worked.
Also current time comparision should be only made when selected date is today date so i made changes like below and used when clause . check below code,
return $roomdata = Room::with(['roomslots' => function ($q) use ($userselectedbookdate, $currenttime) {
$q->when($userselectedbookdate== todaydate(),function($qu) use ($currenttime,$userselectedbookdate){
$qu->where('from_time','>',$currenttime)
->whereDoesntHave('blockedSlots', function($query) use ($userselectedbookdate) {
$query->whereDate('block_date', $userselectedbookdate);
});
})
->whereDoesntHave('blockedSlots', function($query) use ($userselectedbookdate) {
$query->whereDate('block_date', $userselectedbookdate);
});
}])
->where('room_code', $request->room)
->where('active_status', 1)
->first();
Please check and let me know if this is fine.
@Deekshith I cannot test it, but if you are satisfied with the results, looks good to me.
@Nakov Great, thanks for the support.
Please or to participate in this conversation.