空きのある車の一覧を取得する実装で困っています

前提・実現したいこと

社内システムなのですが、空きのある社用車の一覧を取得する部分をLaravelで実装しています。
全ては見せられませんが、一部ERDの抜粋です。

空きのある社用車の一覧を取得する部分の条件を考えているのですが、やりたいことは日本語にすることはできるのですが、条件を反転すると
2020-04-04 13:00迄、かつ、2020-04-04 18:00以降の車の一覧を取得するSQLになってしまいうまくSQLが組み立てられません。

同じような機能を実装された方がいらっしゃいましたらアドバイスいただきたいです。

該当のソースコード

/**
     * @param array $relations
     *
     * @return Car[]|\Illuminate\Database\Eloquent\Builder[]|\Illuminate\Database\Eloquent\Collection
     */
    public function getXxx(array $relations = [])
    {

        /**
         * DB設計的に、車（Car）は複数の予約（Reservations）を持っている。
         * 2020-04-04 13:00 ~ 2020-04-04 18:00の空きのある車の一覧を取得する条件を考える！
         */

        $cars = Car::with(['reservations'])
            ->where('is_abolition', false)
            ->whereHas('reservations', function(\Illuminate\Database\Eloquent\Builder $query) {
                $query->where('start_datetime', '>', '2020-04-04 13:00');
                $query->where('end_datetime', '<', '2020-04-04 18:00');
            })
            ->orderBy('display_priority')
            ->get();

        dd($cars);

        return $cars;
    }

ちなみに予約できる時間は08:00~20:00までです。

試したこと

３日ほどSQL条件考えたり、似たような機能の記事がないか調べたりしていますが、記事もなかなか見つからず困っています。

とりあえず、普通に考えると以下になると思います。

・CarsテーブルとReservationsテーブルをFULL (OUTER) JOIN （完全外部結合）する
・Reservationsテーブルの2020-04-04 13:00 ~ 2020-04-04 18:00なレコードを除外する。
・余ったレコードをCars.idでgroup化する
・これらが空きのある車

レンタカーのような実装は初めてなので
このロジックが適切なのか、そもそもテーブル設計がこれで良いのかという疑問もあります。
このロジックが適切だったとしても、レコードを除外するという部分が自分の中では曲者で、取得とは逆のことをするので...

他にも
ユーザーが同じタイミングで予約してしまった時はサーバー側ではどういう処理をするのが適切なのか
ユーザーが同じタイミングで予約できないようにする（Reservationsテーブルにstatusカラムを持たせておいて、日時入力後の検索結果から該当の車をクリックしたタイミングでstatusを仮としたレコードを追加しておく？）案など
アドバイスいただけると助かります。

補足情報（FW/ツールのバージョンなど）

ここにより詳細な情報を記載してください。

意図した結果にはなりましたが、なぜこうなるのかが頭で理解できていない。人に日本語で説明ができない。。。

m.ts10806

2020/04/04 00:49

クエリビルダに確かwhereBetween　というものがあったはずですが、それでは不足ですか？

mackintosh

2020/04/04 05:15

whereBetweenの存在は分かっているのですが、どういう条件にすれば「この時間〜この時間まで予約の無い車」の一覧を取得できるかの部分で悩んでいます。

行動規範の内容に同意します

回答1件

ベストアンサー

whereDoesntHave を使って以下のようにできないでしょうか。

php
1    // 時間帯が重なる予約を持たないCarに絞る
2    ->whereDoesntHave('reservations', function ($query) {
3        $query->where('end_datetime', '>', '2020-04-04 13:00');
4        $query->where('start_datetime', '<', '2020-04-04 18:00');
5    })

参考: 存在しないリレーションのクエリ

（補足）xは時間帯が重なる例、oは重ならない例

  13:00          18:00
-----+--------------+-----
  <-----> x
                 <-----> x
          <----> x
  <--------------------> x
<--> o
                      <--> o

なお、予約の時間帯には、準備（クルマの移動とか手続など）に要する時間も含まれている必要があるかとは思います。例えば、13時ちょうどに返却されてしまっても、次の人が13時から直ぐに借りれなくなってしまいます。

投稿2020/04/04 01:48

編集2020/04/05 02:44