【メモ】サブクエリ、DB::rawを使って複数テーブルが絡むデータ取得(Laravel)

#開発中サービス
・アイドルファン同士をマッチングするサービス
・ユーザーが好きなアイドルを登録する
・登録されたアイドルが被っているユーザー同士をマッチングする

#テーブル
・users・・ユーザー情報を登録するテーブル
・idols・・ユーザーが登録した「好きなアイドル」
・recommends・・「好きなアイドル」が被っているユーザーの中で既にレコメンドされたユーザーのセット(user_id,friend_id)を登録

#コード
自分と好きなアイドルが被っているユーザーで、かつまだレコメンドされていない(recommendsテーブルに登録されていない)ユーザーのidを取得したい

$friends = DB::select(DB::raw("select id from users where id = any(select user_id from idols where idol = any(select idol from idols where user_id = $user->id)) and id != $user->id and id not in (select friend_id from recommends where user_id = $user->id)"));

#MySQL
サブクエリの結果が１行でない場合、サブクエリをany()で囲うことで複数行に対応できる。
これをしないとエラーになる。
※上記の$user->idのところは１にしている。

select id from users where id = any(select user_id from idols where idol = any(select idol from idols where user_id = 1)) and id != 1;

件数が多くなると遅くなりそうな予感がするので、
もう少しうまくやるやり方を調査中。

#Laravel
SQL文を直接使う場合はDB::rawを使うことでSQL文を生成出来る。
以下のようにすることで$friendsに結果が格納される。
少なくとも結果が複数の場合はstdClassの配列が入りました。(結果が単数の場合は試してません)

$friends = DB::select(DB::raw('select文'));

一つ一つの結果に処理をする場合はforeachで回したけど、
配列の一つ一つの要素がstdClassなので、
取得したカラムを指定して、$friend->idのように書かないと中の値を取り出せなかった。

foreach ($friends as $friend) {
    $recommends = new Recommend();
    $recommends->friend_id = $friend->id;
    $user->recommends()->save($recommends);
}

なんでstdClassになるのかは調査中。

#参考
　https://dev.mysql.com/doc/refman/5.6/ja/subquery-errors.html
　https://qiita.com/10fuya/items/b8ac349ff81b4fa753a6
　https://qiita.com/zaburo/items/627a6b5df539ef6ac33c