Edited at
Laravel 5Day 23

Laravel クエリービルダーでの、サブクエリ(副問合せ)の書き方

More than 3 years have passed since last update.

初投稿です。まだまだ、駆け出しのエンジニアですが、仕事でlaravelを使った開発をしています。

対象のバージョン(動作確認済み)は5.1です。

DBは、Postgresql9.4.4を使用しています。

また説明では、Eloquent ORMは使用しませんが、使用しても動作します。

laravelを使い始めて2年目になりますが、当初困ったのが、サブクエリはどう書くのかということ。

ドキュメントを見ても、サブクエリについて触れられているのはexsist句の時だけでした。

そこで、where句 や where in句 でも使えるよ、という事をご紹介します。


データを用意

最近ハマったアニメ「干物妹!うまるちゃん」を例に、

妹Sが、カラオケに行ったという程で、こんなデータを用意してみました。

妹Sの情報が入ってるテーブル


sisters

 id |          name          

----+------------------------
1 | 土間うまる
2 | 海老名菜々
3 | 本場切絵
4 | 橘・シルフィンフォード

誰が何の曲を歌ったか記録するテーブル


musics

 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

曲と得点、そして何巡目かを記録するテーブル

4人全員が1回歌い終わったら一巡します。


scores

 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文は、こんな感じになるかと思います。


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


Illuminate/Database/Query/Builder.php

    /**

* 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


Illuminate/Database/Query/Builder.php

    /**

* 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);
}

//省略


それぞれ、メソッドの中でクロージャーが渡されたときの処理が書かれています。

ということで、クロージャーを使えばいいのです!

もっと良い書き方があるのかもしれませんが、こんな感じになります。


app/umr.php


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.png

ちゃんとさっきのSQL文と同じ形になりました。

実行して確認してみます。

res1.png

SQLを流した時と、同じ結果が返ってきました。


まとめてみる


  • where句 where in句にクロージャーを渡せる

  • DB::raw()を併用で、複雑なSQL文も書ける

  • 海老名ちゃん歌上手い(笑)
     (点数は適当なので、たまたまです!)

ご指摘や、もっと良いやり方があるという方は、ぜひコメント下さい。