檢索與給定預訂開始和結束時間重疊的所有預訂。Mysql-Laravel

我對這個 sql 的邏輯有點問題，我不知道為什么它會給我一個錯誤，但錯誤在第 4 行“第一個 AND 的開始”。我想做的是檢查給定的開始和結束時間是否對預訂房間有效。我想向用戶展示所有與他想預訂房間的時間段重疊的預訂。

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');
        });

uj5u.com熱心網友回復：

要獲得重疊預訂，您需要更改您的條件（有些是不必要的，有些是錯誤的）

我假設那$time[0]是在$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();

應該這樣做。

uj5u.com熱心網友回復：

這是基于@N69S 的回答，但圍繞三對日期標準和傳遞給閉包的變數進行了額外分組 -

$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();

標籤：数据库拉维

上一篇：在SQL中比較具有相同架構的兩個表中的資料

下一篇：Eloquent從每個組中獲取前5條記錄