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

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

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

# Query BuilderのJOINの基本 :pencil: 

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

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

シンプルにテーブルを結合する場合、非常に簡潔に記述できます :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 |

### 作りたいクエリ

```sql
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することができます。

```php
$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を再利用する方法があります。

```php
$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](https://github.com/satoryu/sakuragakuin)

# 参考

* [How to select from subquery using Laravel Query Builder?](http://findnerd.com/list/view/How-to-select-from-subquery-using-Laravel-Query-Builder/9731/)