laravelでモデルのリレーションで検索機能をつくってみた件

今回したかったこと

社員の出張管理アプリを作成しており、社員がどの場所に出張したか履歴を記録する処理を実装したところ、履歴の検索機能を作りたくなった。

Members（社員）--(1対多)--Histories（履歴）(中間テーブルに相当)--(多対1)--Locations（出張先）

テーブルには上記のようなリレーションが設定されており、この3つのモデルから、Historiesを軸に検索機能を実装したいことがあり挑戦したところ、大変勉強になったのでメモ。

処理の条件

・出張日、社員名、行先場所名で検索する
・3つのうち、リクエスト項目数は0～3でも、どんな組み合わせでも検索できる

テーブル詳細

Historiesというテーブルが中間テーブルに相当します。
ポイントとなる点は、リレーションできるようにHistoriesにはmember_id、location_idというカラムを持っている。

・Membersテーブル
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | int(10) unsigned | YES  |     | NULL    |                |
| name       | varchar(255)     | YES  |     | NULL    |                |
| address    | varchar(255)     | YES  |     | NULL    |                |
| latitude   | double(9,6)      | YES  |     | NULL    |                |
| longitude  | double(9,6)      | YES  |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

　・Historiesテーブル
+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| trip_date   | date             | NO   |     | NULL    |                |
| member_id   | int(10) unsigned | NO   | MUL | NULL    |                |
| start       | int(11)          | NO   |     | NULL    |                |
| location_id | int(10) unsigned | NO   | MUL | NULL    |                |
| distance    | int(11)          | YES  |     | NULL    |                |
| created_at  | timestamp        | YES  |     | NULL    |                |
| updated_at  | timestamp        | YES  |     | NULL    |                |
| deleted_at  | timestamp        | YES  |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------

　・Locationsテーブル
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)     | NO   |     | NULL    |                |
| address    | varchar(255)     | YES  |     | NULL    |                |
| latitude   | double(9,6)      | YES  |     | NULL    |                |
| longitude  | double(9,6)      | YES  |     | NULL    |                |
| created_at | timestamp        | YES  |     | NULL    |                |
| updated_at | timestamp        | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+

モデルにリレーションを定義

モデルのクラス内に、それぞれ下記のリレーションを定義します。

Memberモデル

 public function history(){
      return $this->hasMany(History::class);
 }



Historyモデル

public function member(){
      return $this->belongsTo(Member::class);
}
public function location(){
      return $this->belongsTo(Location::class);
}



Locationモデル

public function history(){
      return $this->hasMany(History::class);
}

ビューへフォームを作成して、ルーティングを通す

Historyの一覧を表示するビューに検索フォームを作る。
リクエストすると、Historyコントローラーを通るようにルーティング処理を書いておく。

フォーム

 {{ Form::open(['method' => 'GET', 'route' => 'history.list']) }}
       {{ Form::input('date', 'trip_date', null, ['placeHolder' => '日付']) }}
       {{ Form::input('text', 'member_name', null, ['placeHolder' => '名前']) }}
       {{ Form::input('text', 'location_name', null, ['placeHolder' => '目的地']) }}
       {{ Form::submit('検索', array('class' => 'btn btn-primary')) }}
 {{ Form::close() }}

ルーティング

 Route::group(['prefix' => '/history', 'as' => 'history.'], function() {
        Route::get('/', [
            'as' => 'list',
            'uses' => 'HistoryController@index'
        ]);
 });

出来上がった処理

飛んできたリクエストを配列で拾い、リクエストあれば検索、なければ全件でif分岐する。
というような感じになっています。今回のポイントになった点は下記の通り。

whenメソッド・・・第1引数がtrueなら、第2引数のクロージャーを処理するようになっている。
本例の場合、出張日がリクエストされていれば、SQLでwhere文を付加するようになっている。

whereHasメソッド・・・第1引数にモデルに定義したリレーションのメソッド名を渡すと、第2引数のクロージャーを処理するようになっている。本例の場合、出張先名、社員名がリクエストされていれば、SQLでwhere～like文を付加するようになっている。

  public function index(Request $request)
    {
        $title = 'History List';
        $search = array(
            'trip_date' => $request->get('trip_date'),
            'member_name' => $request->get('member_name'),
            'location_name' => $request->get('location_name')
        );
        if(array_filter($search)){
            $trip_date = $search['trip_date'];
          //////////今回作った処理/////////
            $histories = History::when($trip_date, function($query, $trip_date){
                return $query->where('trip_date', $trip_date);
            })
            ->whereHas('location',function($q) use ($search) {$q->where('name', 'like', '%'.$search['location_name'].'%');})
            ->whereHas('member',function($q) use ($search) {$q->where('name', 'like', '%'.$search['member_name'].'%');})
            ->get();
         //////////////////////////////
        }else{
            $histories = History::all();
        }
        return view('history/index', ['title' => $title], ['histories' => $histories]);
    }

ちなみに発行されたクエリを確認すると以下のようになっていました。
exists句が使われており、続く()内のサブクエリでレコードが存在しているか見ています。
サブクエリはwhere～likeで実行されているので空白でリクエストしても抽出できるわけですね。

select * from `histories` where `trip_date` = '2020-09-30' 
and exists (select * from `locations` where `histories`.`location_id` = `locations`.`id` and `name` like '%%') 
and exists (select * from `members` where `histories`.`member_id` = `members`.`id` and `name` like '%佐々木%')