Edited at

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

More than 1 year has passed since last update.

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に公開しているのでご活用ください。


参考