理系公務員のプログラミング日記

ホテル予約システムとSQL

ホテル予約システムの基本が出来てきたら、アプリに様々な機能を追加したくなります。（なりますよね？）

すべての部屋の中から予約可能な部屋を検索する機能を作成していきます。 主に考えるべきはどのようなSQL文を作るかです。

指定した日付の範囲で予約可能な部屋を検索する機能

この機能を考えるとき、検索結果として欲しいのは「部屋」になります。 ですので、主テーブルを部屋（rooms）とし、「予約可能な」という条件を 予約（reservations）と予約明細（reservation_details）を使ってフィルタリングします。

SQLでは次のように書けます。

SELECT r.room_id, r.room_type, r.price
FROM rooms AS r
LEFT JOIN reservation_details AS rd ON r.room_id = rd.room_id
LEFT JOIN reservations AS res ON rd.reservation_id = res.reservation_id
WHERE (
    res.reservation_id IS NULL OR 
    (res.start_date > ? OR res.end_date < ?)
)

このSQLをクエリビルダを使って書き換え、コントローラに実装する場合は次の通りです。 元のSQL文に近い書き方で実装することができます。

use Illuminate\Support\Facades\DB;

public function availableRoomsBetweenDates($startDate, $endDate) {
    $rooms = DB::table('rooms')
        ->leftJoin('reservation_details', 'rooms.id', '=', 'reservation_details.room_id')
        ->leftJoin('reservations', 'reservation_details.reservation_id', '=', 'reservations.id')
        ->whereRaw('reservations.id IS NULL OR (reservations.start_date > ? OR reservations.end_date < ?)', [$endDate, $startDate])
        ->select('rooms.id', 'rooms.room_type', 'rooms.price')
        ->get();

    return $rooms;
}

こちらでもOKですが、Eloquentを使ったものに再度置き換えることもできます。

Eloquentのほうが前から素直に文を読めるような気がします。

以下はEloquentに置き換えて、そのままRoomControllerに実装した場合のサンプルです。

RoomController.php

public function availableRoomsBetweenDates($startDate, $endDate) {
   $rooms = Room::leftJoin('reservation_details', 'rooms.id', '=', 'reservation_details.room_id')
        ->leftJoin('reservations', 'reservation_details.reservation_id', '=', 'reservations.id')
        ->where(function($query) use ($startDate, $endDate) {
            $query->whereNull('reservations.id')
                  ->orWhere('reservations.start_date', '>', $endDate)
                  ->orWhere('reservations.end_date', '<', $startDate);
        })
        ->select('rooms.id', 'rooms.room_type', 'rooms.price')
        ->get();

  return $rooms;
}


public function searchByDate(Request $request) {
  $rooms = availableRoomsBetweenDates(
    // 入力フォームから受け取った日付
    $request->startDate,
    $request->$endDate
  );

  return view('表示したいページ',compact('rooms'));
}