初投稿です。まだまだ、駆け出しのエンジニアですが、仕事でlaravelを使った開発をしています。
対象のバージョン（動作確認済み）は5.1です。
DBは、Postgresql9.4.4を使用しています。
また説明では、Eloquent ORMは使用しませんが、使用しても動作します。
laravelを使い始めて2年目になりますが、当初困ったのが、サブクエリはどう書くのかということ。
ドキュメントを見ても、サブクエリについて触れられているのはexsist句の時だけでした。
そこで、where句 や where in句 でも使えるよ、という事をご紹介します。
データを用意
最近ハマったアニメ「干物妹！うまるちゃん」を例に、
妹Sが、カラオケに行ったという程で、こんなデータを用意してみました。
妹Sの情報が入ってるテーブル
id | name
----+------------------------
1 | 土間うまる
2 | 海老名菜々
3 | 本場切絵
4 | 橘・シルフィンフォード
誰が何の曲を歌ったか記録するテーブル
id | sistersid | title
----+-----------+---------------------------------
1 | 1 | かくしん的☆めたまるふぉ～ぜっ！
2 | 2 | かくしん的☆めたまるふぉ～ぜっ！
3 | 3 | かくしん的☆めたまるふぉ～ぜっ！
4 | 4 | かくしん的☆めたまるふぉ～ぜっ！
5 | 1 | ひだまりデイズ
6 | 2 | ひだまりデイズ
7 | 3 | ひだまりデイズ
8 | 4 | ひだまりデイズ
9 | 1 | 勇者うまるの華麗なる生活
10 | 2 | そいだばね
11 | 3 | My Precious
12 | 4 | T・S・F in にっぽん!
13 | 1 | Beautiful Days
14 | 2 | sweet sweet everytime sweet
15 | 3 | トトファンタジア
16 | 4 | シュバッとNo.1
17 | 1 | Sisters Wink
18 | 2 | Sisters Wink
19 | 3 | Sisters Wink
20 | 4 | Sisters Wink
曲と得点、そして何巡目かを記録するテーブル
４人全員が１回歌い終わったら一巡します。
id | musicsid | score | turn
----+----------+-------+------
1 | 1 | 80 | 1
2 | 2 | 95 | 1
3 | 3 | 70 | 1
4 | 4 | 64 | 1
5 | 5 | 50 | 2
6 | 6 | 73 | 2
7 | 7 | 30 | 2
8 | 8 | 84 | 2
9 | 9 | 90 | 3
10 | 10 | 100 | 3
11 | 11 | 33 | 3
12 | 12 | 55 | 3
13 | 13 | 47 | 4
14 | 14 | 91 | 4
15 | 15 | 92 | 4
16 | 16 | 0 | 4
17 | 17 | 90 | 5
18 | 18 | 89 | 5
19 | 19 | 84 | 5
20 | 20 | 56 | 5
あえて、サブクエリを使わないと取得できないような、データ構成にしました。
では、上のデータから「巡毎に誰が一番得点が高くて、何の曲を歌ったか」を取得してみます。
SQL文は、こんな感じになるかと思います。
select s.turn, si.name, mu.title, s.score from scores s
inner join musics mu on s.musicsid = mu.id
inner join sisters si on si.id = mu.sistersid
where (s.turn, s.score) in (select turn, max(score) from scores group by turn);
一応これを流して結果を確認してみる。
turn | name | title | score
------+-----------------------+---------------------------------+-------
1 | 海老名菜々 | かくしん的☆めたまるふぉ～ぜっ！ | 95
2 | 橘・シルフィンフォード | ひだまりデイズ | 84
3 | 海老名菜々 | そいだばね | 100
4 | 本場切絵 | トトファンタジア | 92
5 | 土間うまる | Sisters Wink | 90
問題なさそう。
それでは本題
where 句とwhere in句でのサブクエリの書き方ですが、まずは、それぞれのメソッドの中身を見てみます。
- where
/**
* Add a basic where clause to the query.
*
* @param string|array|\Closure $column
* @param string $operator
* @param mixed $value
* @param string $boolean
* @return $this
*
* @throws \InvalidArgumentException
*/
public function where($column, $operator = null, $value = null, $boolean = 'and')
{
// 中略
// If the value is a Closure, it means the developer is performing an entire
// sub-select within the query and we will need to compile the sub-select
// within the where clause to get the appropriate query record results.
if ($value instanceof Closure) {
return $this->whereSub($column, $operator, $value, $boolean);
}
- whereIn
/**
* Add a "where in" clause to the query.
*
* @param string $column
* @param mixed $values
* @param string $boolean
* @param bool $not
* @return $this
*/
public function whereIn($column, $values, $boolean = 'and', $not = false)
{
$type = $not ? 'NotIn' : 'In';
// If the value of the where in clause is actually a Closure, we will assume that
// the developer is using a full sub-select for this "in" statement, and will
// execute those Closures, then we can re-construct the entire sub-selects.
if ($values instanceof Closure) {
return $this->whereInSub($column, $values, $boolean, $not);
}
//省略
それぞれ、メソッドの中でクロージャーが渡されたときの処理が書かれています。
ということで、クロージャーを使えばいいのです！
もっと良い書き方があるのかもしれませんが、こんな感じになります。
public function getKaraokeResult()
{
return DB::table('scores as s')->select('s.turn', 'si.name', 'mu.title', 's.score')
->join('musics as mu', 'mu.id', '=', 's.musicsid')
->join('sisters as si', 'si.id', '=', 'mu.sistersid')
->whereIn(DB::raw('(s.turn, s.score)'),
function ($query)
{
$query->select('turn', DB::raw('max(score)'))
->from('scores')
->groupBy('turn');
})
->get();
}
whereIn()は複数列に対応していないので、一部raw()を使って直に書いています。
このraw()とクロージャーの組み合わせで、より複雑なSQL文もクエリービルダーで書けると思います。
また、テーブルの別名についてですが、asを省略して書くとエラーになります。
それと余談ですが、
Eloquentのsoftdeleteを使用している場合、自動で「deleted_at is null」が補完されますが、クロージャー内のクエリには補完されません。
では、実行する前に、生成されるSQL文をtoSql()で確認してみます。
このメソッドを使うと、発行されるSQL文が分かるので超便利です。
ちゃんとさっきのSQL文と同じ形になりました。
実行して確認してみます。
SQLを流した時と、同じ結果が返ってきました。
まとめてみる
- where句 where in句にクロージャーを渡せる
- DB::raw()を併用で、複雑なSQL文も書ける
- 海老名ちゃん歌上手い(笑) (点数は適当なので、たまたまです！)
ご指摘や、もっと良いやり方があるという方は、ぜひコメント下さい。