Laravel リレーションテーブルの複数の列に対して、複数キーワードでAND検索をする方法

やりたいこと

• 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だけで実装する方法がもしかしたらあるかもしれません。