Reputation: 2233
I want to save two time interval in my database. where it will check the database and if someone already booked that time it won't save the time whereas if it's empty it will save time which user will give as a input.
Eg. A user want to book the schedule 8:00 to 8:30, while saving into the database it will check in the database whether someone already take that time or not, if it's not then it will save otherwise it won't. Meanwhile user can't give input even in 8:15 also.
How do i solve this overlapping problem?
here is the controller code I have used, it doesn't running though:
public function postAllocateRoom(Request $request)
{
$classRoom = new ClassRoom();
$classRoom->department_id=$request->Input(['department_id']);
$classRoom->room_id=$request->Input(['room_id']);
$classRoom->course_id=$request->Input(['course_id']);
$classRoom->day_id=$request->Input(['day_id']);
$classRoom->start=$request->Input(['start']);
$classRoom->end=$request->Input(['end']);
$startTime = Carbon::parse($request->input('start'));
$endTime = Carbon::parse($request->input('end'));
$classRoomCount = ClassRoom::where(function ($query) {
$query->where('start', '>=', $startTime)
->where('end', '<=', $startTime); })->count();
$messages ="Class Room Already Taken";
if ($classRoomCount > 0) {
return redirect('allocateRoomPage',$message);
}
else {
$classRoom->save();
return redirect('allocateRoomPage');
}
}
Upvotes: 1
Views: 1149
Reputation: 11689
The rule for time overlapping is simple (see here for a complete graphic explanation):
start1 < end2 AND end1 > start2
So your query can be:
$classRoomCount = ClassRoom::where
(
function( $query ) use( $startTime, $endTime )
{
$query->where( 'start', '<', $endTime )
->where( 'end', '>', $startTime);
}
)->count();
Upvotes: 1
Reputation: 35190
Firstly, to be able to have access to $startTime
and $endTime
within the query closure you will need to pass them through using the use
construct i.e.
function ($query) use ($startTime, $endTime)
The following should work to get the correct counts for classrooms booked between certain times:
$classRoomCount = ClassRoom::where(function ($query) use ($startTime, $endTime) {
$query
->where(function ($query) use ($startTime, $endTime) {
$query
->where('start', '>=', $startTime)
->where('end', '<', $startTime);
})
->orWhere(function ($query) use ($startTime, $endTime) {
$query
->where('start', '<', $endTime)
->where('end', '>=', $endTime);
});
})->count();
Hope this helps!
Upvotes: 0