Check date range is not already booked using Laravel 8?

Question

I am working in an online room booking system where the customer can book a room for a particular date-time range. If another customer books the same room for a specified date-time range, I have to check whether the room is already booked or not. My database table booking looks like this:

my booking table

BookingController

    public function memberBookingSlotViewStore(Request $request,$random_code){

        $roomId = Room::where('random_code',$random_code)->first();
        $startDateTime = $startDateTime = date('Y-m-d H:i', strtotime("$request->start_date, 
       $request->start_time"));
        $endDateTime = date('Y-m-d H:i', strtotime("$request->end_date, $request->end_time"));

           $similarBookingExists = Booking::query()
             ->where('room_id', $request->roomId)
                ->where(function (Builder $query) use ($startDateTime, $endDateTime) {
                $query->where(function (Builder $query) use ($startDateTime) {
                    $query->where('start_datetime', '<=', $startDateTime)
                        ->where('end_datetime', '>=', $startDateTime);
                })
                ->orWhere(function (Builder $query) use ($endDateTime) {
                    $query->where('start_datetime', '<=', $endDateTime)
                ->where('end_datetime', '>=', $endDateTime);
            });
            })->exists();

        if($similarBookingExists){

            return redirect()->back()->withInput()->with('errorMsg', 'Slot Not Available.');
        }
        else{
            $bookings                   = new Booking();
            $bookings->user_id          =  Auth::user()->id;
            $bookings->room_id          =  $roomId->id;
            $bookings->start_datetime   =  $startDateTime;
            $bookings->end_datetime     =  $endDateTime;
            $bookings->points           =  $request->points;
            $bookings->save();
            return redirect()->route('booking.member-booking-summary',$random_code);
        }
    }

Unfortunately, the code does not seem to work. It does not check the condition properly. How to do the desired check properly using the given inputs?

Answer 1

Can you do something like this :

public function (Request $request,$random_code){
    $room = Room::where('random_code',$random_code)->first();
    
    $start_datetime = \Carbon::parse($request->start_date . " " . $request->start_time)->toDateTimeString(); 

    $end_datetime = \Carbon::parse($request->end_date . " " . $request->end_time)->toDateTimeString();
    $bookingOnOverLappingTime = Booking::where('room_id', $room->id)->where('start_datetime', '<=', $start_datetime)->where('end_datetime', '>=', $end_datetime)->get();

    if($bookingOnOverLappingTime->count() > 0){
        //booking exists at selected time for the given room
        return back()->withError(['errorMsg' => 'Slot Not Available.']);
        // withError is essentially like throwing a validation error
    }

    else{
        //Booking slot available. Proceed with booking
    }        
}