何かの管理画面を作る時、管理者がデータを検索する機能を作る時がよくあります。この記事ではそのような時に使えるリクエストで挙動が変わる検索クエリの書き方について紹介します。
Laravel には when という条件を満たすとコールバックでクエリ構築をするメソッドがあります。これを使うことで次のように検索クエリを書くことができます。
11.x データベース：クエリビルダ Laravel#conditional-clauses
// このような検索リクエストあるとします。
// この検索条件はユーザーID、名前、メールアドレス、メモ、メモ番号、作成日時の範囲を指定できます。
$searchRequest = \Validator::make([
'name' => '浜松太郎',
'memo' => '',
'memoNumber' => 0,
// DB中はAsia/Tokyoのタイムゾーンで保存されているけれども
// リクエストはUTCで来たという想定です。
'createdAtStart' => '2021-01-01T23:02:20.078Z',
], [
'userId' => ['nullable', 'integer'],
'name' => ['nullable', 'string'],
'memo' => ['nullable', 'string'],
'memoNumber' => ['nullable', 'integer'],
'createdAtStart' => ['nullable', 'date'],
'createdAtEnd' => ['nullable', 'date'],
])->validate();
$sql = \DB::query()->from('users')
// リクエストの該当する項目に値があれば、その値でWHEREする
->when($searchRequest['userId'] ?? null, fn($q, $v) => $q->where('user_id', $v))
->when($searchRequest['name'] ?? null, fn($q, $v) => $q->where('name', $v))
->when($searchRequest['memo'] ?? null, fn($q, $v) => $q->where('memo', $v))
->when($searchRequest['memoNumber'] ?? null, fn($q, $v) => $q->where('memoNumber', $v))
->toRawSql();
$this->info($sql);
// select * from `users` where `name` = '浜松太郎'
比較的読みやすいのですが問題点として数値の0が入力された場合にそのままスキップする条件に含まれてしまう点があります。GET経由ですと文字列になりますが、POSTやコマンドラインや他の処理から呼ばれるなどすると問題が起きる時があります。これを避けるために次のように条件をより明確にチェックすることが有効です。
$sql = \DB::query()->from('users')
->when(isset($searchRequest['userId']), fn($q) => $q->where('user_id', $searchRequest['userId']))
->when(isset($searchRequest['name']) && $searchRequest['name'] !== '', fn($q) => $q->where('name', $searchRequest['name']))
->when(isset($searchRequest['memo']) && $searchRequest['memo'] !== '', fn($q) => $q->where('memo', $searchRequest['memo']))
->when(isset($searchRequest['memoNumber']), fn($q) => $q->where('memo_number', $searchRequest['memoNumber']))
->toRawSql();
$this->info($sql);
// select * from `users` where `name` = '浜松太郎' and `memo_number` = 0
スキップの条件部をboolにし実行されるクエリビルダに直に値を渡す形にすることでより正確に自在にクエリを構築できます。
上記の例は単純な等価比較なので直書きしましたが、次のように全てを直に書くと読み難くなる場合もあります。
$sql = \DB::query()->from('users')
->when(isset($searchRequest['createdAtStart']), static function ($query) use ($searchRequest) {
// 日時をLaravelで扱うタイムゾーンとして扱うようにしてWHEREに渡します。
try {
$dateTimeValue = new \DateTime($searchRequest['createdAtStart']);
$dateTimeValue->setTimezone(new \DateTimeZone(config('app.timezone')));
} catch (\Exception $e) {
// 日付生成エラーを握りつぶし日付検索条件を無視して検索続行
return $query;
}
return $query->whereDate('created_at', '>=', $dateTimeValue->format('Y-m-d'));
})
->when(isset($searchRequest['createdAtEnd']), static function ($query) use ($searchRequest) {
try {
$dateTimeValue = new \DateTime($searchRequest['createdAtEnd']);
$dateTimeValue->setTimezone(new \DateTimeZone(config('app.timezone')));
} catch (\Exception $e) {
// 日付生成エラーを握りつぶし日付検索条件を無視して検索続行
return $query;
}
return $query->whereDate('created_at', '<=', $dateTimeValue->format('Y-m-d'));
})
->toRawSql();
$this->info($sql);
// select * from `users` where date(`created_at`) >= '2021-01-02'
文字列で渡された”2021-01-01T23:02:20.078Z”を”2021-01-02T08:02:20.078+09:00″として扱うように挑戦して、日時として成立するならば”Y-m-d”フォーマットにして日付で検索するという処理です。ちょっとした処理ではありますが、このサイズを日時関連のwhere全てに書くとコードの見通しが悪くなってしまいます。これを解決するためには次のようにクロージャを返すメソッドや関数を作る方法が取れます。
/**
* 検索条件のクロージャを生成する関数群
*/
class SearchWhereMacro
{
/**
* WHERE date(${column}) ${operator} ${value}
* @param string $column 検索対象のカラム
* @param string $operator 比較演算子
* @param mixed $value 検索値
* @return \Closure
*/
public static function date(string $column,string $operator, mixed $value): \Closure
{
return static function (\Illuminate\Database\Query\Builder $query) use ($column, $value, $operator) {
try {
$dateTimeValue = new \DateTime($value);
$dateTimeValue->setTimezone(new \DateTimeZone(config('app.timezone')));
} catch (\Exception $e) {
// 日付生成エラーを握りつぶし日付検索条件を無視して検索続行
return $query;
}
return $query->whereDate($column, $operator, $dateTimeValue->format('Y-m-d'));
};
}
}
// ↑のクロージャを作る関数を呼ぶことでクエリ本体を小さく書けます
$sql = \DB::query()->from('users')
->when(isset($searchRequest['createdAtStart']), SearchWhereMacro::date('created_at', '>=', $searchRequest['createdAtStart'] ?? null))
->when(isset($searchRequest['createdAtEnd']), SearchWhereMacro::date('created_at', '<=', $searchRequest['createdAtEnd'] ?? null))
->toRawSql();
$this->info($sql);
// select * from `users` where date(`created_at`) >= '2021-01-02'
加えてWHEREをスキップする条件についてもまとめることができます。
function shouldSkip($value): bool
{
return isset($value) && $value !== '' && $value !== [];
}
$sql = \DB::query()->from('users')
->when(shouldSkip($searchRequest['userId'] ?? null), fn($q) => $q->where('user_id', $searchRequest['userId']))
->when(shouldSkip($searchRequest['name'] ?? null), fn($q) => $q->where('name', $searchRequest['name']))
->when(shouldSkip($searchRequest['memo'] ?? null), fn($q) => $q->where('memo', $searchRequest['memo']))
->when(shouldSkip($searchRequest['memoNumber'] ?? null), fn($q) => $q->where('memo_number', $searchRequest['memoNumber']))
->when(shouldSkip($searchRequest['createdAtStart'] ?? null), SearchWhereMacro::date('created_at', '>=', $searchRequest['createdAtStart'] ?? null))
->when(shouldSkip($searchRequest['createdAtEnd'] ?? null), SearchWhereMacro::date('created_at', '<=', $searchRequest['createdAtEnd'] ?? null))
->toRawSql();
$this->info($sql);
// select * from `users` where `name` = '浜松太郎' and `memo_number` = 0 and date(`created_at`) >= '2021-01-02'
こうなってくると存在しないインデックス参照エラーを防いでいる?? nullも削りたくなります。これは次のように optional を使うことができます。
$searchRequestOpt = optional($searchRequest);
$sql = \DB::query()->from('users')
->when(shouldSkip($searchRequestOpt['userId']), fn($q) => $q->where('user_id', $searchRequestOpt['userId']))
->when(shouldSkip($searchRequestOpt['name']), fn($q) => $q->where('name', $searchRequestOpt['name']))
->when(shouldSkip($searchRequestOpt['memo']), fn($q) => $q->where('memo', $searchRequestOpt['memo']))
->when(shouldSkip($searchRequestOpt['memoNumber']), fn($q) => $q->where('memo_number', $searchRequestOpt['memoNumber']))
->when(shouldSkip($searchRequestOpt['createdAtStart']), SearchWhereMacro::date('created_at', '>=', $searchRequestOpt['createdAtStart']))
->when(shouldSkip($searchRequestOpt['createdAtEnd']), SearchWhereMacro::date('created_at', '<=', $searchRequestOpt['createdAtEnd'] ))
->toRawSql();
$this->info($sql);
// select * from `users` where `name` = '浜松太郎' and `memo_number` = 0 and date(`created_at`) >= '2021-01-02'
optional は Laravel のヘルパ関数の一つで、与えた値から伸びるプロパティやインデックスが存在しない場合も null を返すだけにする関数です。これを使うことで単に$searchRequestOpt['userId']と書いてもエラーが起きなくなります。
11.x ヘルパ Laravel#method-optional
こんな感じでよく使う処理をメソッドにまとめ、検索条件を使いやすい形にし、それを when に渡すことで自在に変化する検索クエリを簡単に書けます。例では\DB::query()を使っていますがUser::query()のような Eloquent 起点の検索クエリでも同様に書けます。またWHEREのみを使っていますが、ORDER BYについても同様に書けます。
あんまりやりすぎると変数の意味が分からなくなって惨事になりますが、検索条件の入った変数の名前を短くする、という手も更に取れます。
$r = optional($searchRequest);
$sql = \DB::query()->from('users')
->when(shouldSkip($r['userId']), fn($q) => $q->where('user_id', $r['userId']))
->when(shouldSkip($r['name']), fn($q) => $q->where('name', $r['name']))
->when(shouldSkip($r['memo']), fn($q) => $q->where('memo', $r['memo']))
->when(shouldSkip($r['memoNumber']), fn($q) => $q->where('memo_number', $r['memoNumber']))
->when(shouldSkip($r['createdAtStart']), SearchWhereMacro::date('created_at','>=', $r['createdAtStart']))
->when(shouldSkip($r['createdAtEnd']), SearchWhereMacro::date('created_at', '<=', $r['createdAtEnd']))
->toRawSql();
$this->info($sql);
// select * from `users` where `name` = '浜松太郎' and `memo_number` = 0 and date(`created_at`) >= '2021-01-02'
