search
LoginSignup
80
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

Laravel 5 Advent Calendar 2015 Day 23

posted at

updated at

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

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

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
80
Help us understand the problem. What are the problem?