Retrieve all the bookings that overlap with a given booking start and end time. Mysql

I am having a little trouble with the logic of this sql, I don't know why it's given me an error but the error is at line 4 "the start of the first AND". What I am trying to do is to check if the given start and end time are valid to book a room. I wanna show the user all the bookings that will overlap with the period he wanna book the room in.

select * from `bookings` 
where (
    `room_id` = 4 
       and (`starting_time` < 2022-11-16 23:07:55 
             and `ending_time` > 2022-11-16 23:07:55
             and `starting_time` < 2022-11-17 00:07:55 
             and `ending_time` > 2022-11-17 00:07:55) 
       or (`starting_time` < 2022-11-16 23:07:55 
             and `ending_time` > 2022-11-16 23:07:55
             and `starting_time` < 2022-11-17 00:07:55 
             and `ending_time` < 2022-11-17 00:07:55)
       or ( `starting_time` > 2022-11-16 23:07:55 
             and `ending_time` > 2022-11-16 23:07:55
             and `starting_time` < 2022-11-17 00:07:55 
             and `ending_time` > 2022-11-17 00:07:55)
      )

 $bookings = Booking::where('room_id', $room_id)
            ->Where(function ($query) use ($times) {
                $query->where('starting_time', '<', $times[0])
                    ->where('ending_time', '>', $times[0])
                    ->where('starting_time', '<', $times[1])
                    ->where('ending_time', '>', $times[1]);
            })
            ->orWhere(function ($query) use ($times) {
                $query->where('starting_time', '<', $times[0])
                    ->where('ending_time', '>', $times[0])
                    ->where('starting_time', '<', $times[1])
                    ->where('ending_time', '<', $times[1]);
            })
            ->orWhere(function ($query) use ($times) {
                $query->where('starting_time', '>', $times[0])
                    ->where('ending_time', '>', $times[0])
                    ->where('starting_time', '<', $times[1])
                    ->where('ending_time', '>', $times[1]);
            })
            ->get();


Schema::create('bookings', function (Blueprint $table) 
{ 
  $table->bigIncrements('id'); 
  $table->datetime('starting_time'); 
  $table->datetime('ending_time')->nullable(); 
  $table->string('guest_name')->nullable(); 
  $table->string('guest_phone')->nullable(); 
  $table->longText('comments')->nullable(); 
  $table->timestamps(); $table->softDeletes(); 
});
 Schema::table('bookings', function (Blueprint $table) {
            $table->unsignedBigInteger('room_id')->nullable();
            $table->foreign('room_id', 'room_fk_7600582')->references('id')->on('rooms');
            $table->unsignedBigInteger('team_id')->nullable();
            $table->foreign('team_id', 'team_fk_7547221')->references('id')->on('teams');
        });

CodePudding user response：

To get overlapping booking, you need to change your conditions (some are unnecessary and others are wrong)

I'm supposing that $time[0] is prior to $time[1]

$startingTime = $time[0];
$endingTime = $time[1];
$bookings = Booking::where('room_id', $room_id)
       ->where(function ($query) use ($startingTime , $endingTime) {
            $query->where(function ($query) use ($startingTime , $endingTime) {
                $query->where('starting_time', '>=', $startingTime)
                    ->where('ending_time', '<=', $endingTime);
            })
            ->orWhere(function ($query) use ($startingTime) {
                $query->where('starting_time', '<=', $startingTime)
                    ->where('ending_time', '>', $startingTime);
            })
            ->orWhere(function ($query) use ($endingTime) {
                $query->where('starting_time', '<', $endingTime)
                    ->where('ending_time', '>=', $endingTime);
            });
       })
       ->get();

That should do it.

CodePudding user response：

This is based on @N69S answer but with the additional grouping around the three pairs of date criteria and the variables being passed to the closures -

$startingTime = $times[0];
$endingTime = $times[1];
$bookings = Booking::where('room_id', $room_id)
            ->where(function ($query) use ($startingTime, $endingTime) {
                $query->where(function ($query) use ($startingTime, $endingTime) {
                    $query->where('starting_time', '>=', $startingTime)
                        ->where('ending_time', '<=', $endingTime);
                })
                ->orWhere(function ($query) use ($startingTime) {
                    $query->where('starting_time', '<=', $startingTime)
                        ->where('ending_time', '>', $startingTime);
                })
                ->orWhere(function ($query) use ($endingTime) {
                    $query->where('starting_time', '<', $endingTime)
                        ->where('ending_time', '>=', $endingTime);
                })
            })
            ->get();