PHP
SQL
SQLServer
laravel

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

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

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

Query BuilderのJOINの基本 :pencil:

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

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

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

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

生徒のテーブル 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("({$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に公開しているのでご活用ください。

参考