やりたいこと
- Laravelアプリでkeywordによるフィルタ検索をしたい。
- keywordは複数入力可能で、AND検索とする。
- 検索対象の列は、親テーブルの列+子テーブルの列とする。
- keywordに半角スペースが含まれないものとする。
⇒keyword検索自体はWEBで調べると出てきますが、複数keywordかつリレーションテーブルレコードの検索パターンが載ってませんでしたので、いろいろ組み合わせてどうにか実装できました。
検索例
・parentsテーブル [ parent_id, parent_text ]
・childlenテーブル [ child_id, parent_id(外部キー), child_text ]
parent_text | child_text | keyword1 | keyword2 | Hit |
---|---|---|---|---|
おはよう | こんにちは | おは | にちは | 〇 |
おはよう | こんにちは | おは | よう | 〇 |
おはよう | こんにちは | こん | にちは | 〇 |
おはよう | こんにちは | おは | ばんは | × |
ソースコード
use Illuminate\Support\Facades\DB;
$keywordList = ["おは", "にちは"];
$result = DB::table('parents')
->join('children', 'parents.parent_id', 'children.parent_id')
->where(function($query) use($keywordList){
foreach ($keywordList as $keyword) {
$query->where(DB::raw('CONCAT(parents.parent_text, " ", children.child_text)'), 'like', '%'.$keyword.'%');
}
});
var_dump($result->toSql()); // SQL文確認
$records = $result->get();
LaravelでDB操作する場合、クエリビルダかEloquentを使用しますが、Eloquentだけでは細かいSQL生成ができないため、クエリビルダを使用しました。
生成されたSQL
select * from parents
inner join children parents.parent_id = children.parent_id
where (
CONCAT(parents.parent_text, " ", children.child_text) like ? and
CONCAT(parents.parent_text, " ", children.child_text) like ?
);
?には検索キーワードが入ります。CONCATで検索対象の文字列を半角スペースを挟んで結合しています。これでどちらの列にキーワードが入っていても検索に引っかかるようになります。
結果をEloquent形式にしたい場合
・クエリビルダとEloquentでは、getしたオブジェクトの構造に若干の違いがありview表示側で修正がいる。
・クエリビルダではリレーションテーブルがたくさんあるとjoin文の記述が面倒。
などの理由から、Eloquent形式で取得したい。
use Illuminate\Support\Facades\DB;
use App\Parent; // Eloquent用モデル
/*
$keywordList = ["おは", "にちは"];
$result = DB::table('parents')
-> ・・・
省略
*/
// クエリビルダの検索結果からparent_id列のみ取得
$parentIds = $result->pluck('parents.parent_id');
// Eloquentでレコード取得
$parents = Parent::with('child');
// Eloquentレコードをクエリビルダの検索結果IDでフィルタリング
$records = $parents->whereIn('parent_id', $parentIds)->get();
クエリビルダの検索結果の主キーリストをEloquentで検索しています。
最後に
クエリビルダを使用せずにEloquentだけで実装する方法がもしかしたらあるかもしれません。