Laravelはwithで勝手にリレーションを貼ってくれるので便利なのですが、実はこれ実際にJOINしているわけではないので、リレーション先のテーブルでSELECTしたりWHEREしたりすることができません。

動かない

$hoge = Hoge::query();
$hoge->with('fuga');   // hogeはfugaにhasOneしている
$hoge->select(['hoge.*', 'fuga.hoge_id', 'fuga.column1']);
$hoge->where('fuga.column1', '=', $column1);

これはエラーになります。
実はwithは、まずhogeテーブルからSELECTし、そのidでfugaをWHERE id INでSELECTしています。
WHEREは普通に書くことができません。
こういう場合はjoinを使わなければならないようです。

JOINがあるなら話は簡単だ、ということでこう書きたくなるじゃないですか。

$hoge = Hoge::query();

$hoge->join('fuga', function (JoinClause $join) use ( $column1 ){
    $join->on('hoge.id', '=', 'fuga.hoge_id');
    $join->select(['fuga.hoge_id', 'fuga.column1']);
    $join->where('fuga.column1', '=', $column1);
});

期待するSQLはこうです。

SELECT hoge.*, fuga.hoge_id, fuga.column1, fuga.column2 FROM hoge
 LEFT JOIN fuga ON hoge.id=fuga.hoge_id WHERE fuga.column1=xxxxx

しかし、これ実は効きません。
selectメソッドが完全に無視されてSELECT * FROMになります。
エラーが出ればまだマシなのに、何も言わずに成功しているように見えるのが厄介です。
なんで？

さて、これでどうなるかというと、idがfuga.idになる事故が発生します。

対処するには、こう書かなければなりません。

$hoge = self::query();
$hoge->select(['hoge.*', 'fuga.id AS fuga_id', 'fuga.hoge_id', 'fuga.column1', 'fuga.column2']);

$hoge->join('fuga', function (JoinClause $join) {
    $join->on('hoge.id', '=', 'fuga.hoge_id');
});

なんで？

joinとwithを一緒に使うとバグる

この項目、『普通に』書くだけでバグるのでだいぶ深刻だと思うのですが、事例を探しても見当たりませんでした。

$foo = Foo::query();

$foo->join('bar', function (JoinClause $join) {
    $join->on('foo.id', '=', 'bar.foo_id');
});
$foo->with('baz'); // fooはbazにhasOneしている

期待しているのはWHERE baz.foo_id = foo.idなわけですが、実際はWHERE baz.foo_id = bar.idとかいう謎のSQL…相当の動作がおきます。

まずjoinしたときにidがfoo.idからbar.idに上書きされてしまい、その後bar.idを使ってbazとwithするので意味のわからない結果が出てくることになります。

Laravelのクエリビルダ、テーブルがひとつふたつの単純なテーブルを扱うのは非常に簡単なんだけど、ふたつ以上のリレーションを組み合わせたり、入り組んだSQLを扱おうとすると途端に超絶難解になってつらい。
あとEloquentとクエリビルダの違いがわからない。