サブクエリをJOINするSQLをQuery Builderで作る

Laravel のQuery Builderを使うと、生のSQLをなるべく書かずにすませることができる。例えば、クエリの生成するロジックが複雑な場合、文字列連結を多用しながらSQLの文法を守りながら組み立てなければならないため難しいが、Query Builderを用いることで構造を意識するだけでよくなり、最終的にSQL文として正しいクエリを生成してくれる。

と、便利そうなのだけれど、複雑なクエリになると少々大変です
ここでは、先日ちょうど苦労したので、そのサブクエリをJOINさせる方法について紹介します。

Query BuilderのJOINの基本

\DB::table('users')->select('id', 'name')
  ->join('profiles', 'profiles.user_id', '=', 'users.id');

のように、joinメソッドに、JOINするテーブル名、結合する条件を続けて記述します。デフォルトはInner Joinで、LeftやRightはそれぞれleftJoinとrightJoinメソッドが提供されています。

シンプルにテーブルを結合する場合、非常に簡潔に記述できます

例: 生徒ごとの各年度ごとのテストの順位

生徒のテーブル students

id	name
1220	Suzuka Nakamoto
0620	Yui Mizuno
0704	Moa Kikuchi

学年末テストの点数 scores

year	student_id	score
2010	1220	8.5
2010	0620	7
2010	0704	7
2011	1220	3
2011	0620	5
2011	0704	4
2012	1220	8.5
2012	0620	6
2012	0704	11
2013	0620	5
2013	0704	9
2014	0620	12
2014	0704	7.5

作りたいクエリ

SELECT 
  students.name, 
  year2010.rank AS rank2010, 
  year2011.rank AS rank2011, 
  year2012.rank AS rank2012
FROM students
LEFT JOIN(
   SELECT student_id, RANK() OVER (PARTITION BY year ORDER BY score DESC) AS rank FROM scores
   WHERE year = 2010
) AS year2010 ON year2010.student_id = students.id
LEFT JOIN(
   SELECT student_id, RANK() OVER (PARTITION BY year ORDER BY score DESC) AS rank FROM scores
   WHERE year = 2011
) AS year2011 ON year2011.student_id = students.id
LEFT JOIN(
   SELECT student_id, RANK() OVER (PARTITION BY year ORDER BY score DESC) AS rank FROM scores
   WHERE year = 2012
) AS year2012 ON year2012.student_id = students.id

Query Builder を使ってサブクエリー生成する

DB::raw を使う場合

DB::raw を使うと文字列として渡したSQL文を作ることができます。
サブクエリをDB::rawで作成し、それをjoin メソッド（ここではleftJoin）に渡せば、サブクエリをjoinすることができます。

$students = Student::select('name', 'year2010.rank AS rank2010');
$sub_query = DB::raw("SELECT student_id, RANK() OVER (PARTITION BY year ORDER BY score DESC) AS rank WHERE year = 2010");

$students->leftJoin("({$sub_query}) AS year2010",
  'year2010.student_id', '=', 'students.id');

しかし、変数をバインディングしたい場合は、SQL文を組み立てる文字列を生成するコードを書かなければなりません。

Query Builder のメソッドで生成する場合

リレーションや他でSQL文を作成するメソッドなど、既存のQuery Builderを再利用する方法があります。

$students = Student::select('name', 'year2010.rank AS rank2010');

$sub_query = Score::select("student_id", "RANK() OVER (PARTITION BY year ORDER BY score DESC) AS rank")
  ->where("year", 2010);

$students->leftJoin(\DB::raw("({$sub_query->toSql()}) AS year2010"),
  'year2010.student_id', '=', 'students.id');
$students->mergeBindings($sub_query->getQuery());

$sub_queryを生成するところがDB::rawではなくQuery Builderにより生成したオブジェクトに変わりました。通常のQuery Builderと使い方はそのままです。
それをjoinさせる時に、toSqlメソッドでSQL文を生成し、文字列に埋め込んでいます。これは、AS year2010 のようにエイリアスを指定する引数がjoinメソッドに存在していないため、致し方がなくこうしています。
最後に、$sub_queryに渡したバインディング引数をjoin先のクエリーに渡すため、mergeBindingsを呼んでいます。

こうすることで、SQL文を文字列として生成するプログラムをなるべく書かずに、Query Builderに任せることができます。

サンプルコード

上のサンプルのコードは、Githubに公開しているのでご活用ください。

• satoryu/sakuragakuin

参考

• How to select from subquery using Laravel Query Builder?