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

  • 27
    いいね
  • 0
    コメント
この記事は最終更新日から1年以上が経過しています。

初投稿です。まだまだ、駆け出しのエンジニアですが、仕事で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文も書ける
  • 海老名ちゃん歌上手い(笑)  (点数は適当なので、たまたまです!)

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

この投稿は Laravel 5 Advent Calendar 201523日目の記事です。