mysql - 创建 SQL 查询

Question

我有一个查询问题..基本上我的酒店预订网站上都有Reservations预订有入住日期和退房日期

.

我需要一个 SQL 查询

detects if a new check in and checkout date is in the range of any check in and check out date in the table.

我当前的查询不起作用，因为它无法检测新输入的 checkin 日期何时在数据库 checkin 日期之前开始以及新输入的 checkout 日期何时在数据库中的 checkout 日期之后结束。

感谢您的帮助!

当前代码(我不确定您是否想要纯 sql 或...

// Gets the Checkin and checkout dates from the session variable
        $FirstDate = $request->session()->get('checkin');
        $SecDate = $request->session()->get('checkout');

        // Tries to match the checkin and checkout dates with other reservations of the same Hotel room
        foreach ($Rooms as $Room) {
            $Id =  $Room->id;

            $RoomsBooked = Reservation::where('room_id','=', $Id)
            ->where('CheckIn','>=',$FirstDate)
            ->where('CheckOut','<=',$SecDate)

                ->orWhere(function($query) use ($FirstDate,$Id)
                    {
                      $query->where('room_id','=', $Id)
                            ->where('CheckIn','<=',$FirstDate)

                            ->where('CheckOut','>=',$FirstDate);
                          })

                            ->orWhere(function($query2) use ($FirstDate,$SecDate,$Id)
                            {
                              $query2->where('room_id','=', $Id)
                                    ->where('CheckIn','>=',$FirstDate)

                                    ->where('CheckIn','<=',$SecDate);
                                  })->count();


                  // Works out How many rooms are available
                  $Roomsavailable = $Room->TotalRooms;
                  $Roomsleft =  $Roomsavailable - $RoomsBooked;
                  $Room->spaceleft = $Roomsleft;
            }

Answer 1

检查两个日期范围是否重叠的基本逻辑很简单:

where start1 < end2 and end1 > start2

替换<与 <=等等，如果您希望相同的日期算作重叠。

因此，在您的代码中，我认为您只需要一个单个位置来检查:

        ->where('CheckIn','<=',$SecondDate)
        ->where('CheckOut','>=',$FirstDate);