from句のサブクエリ

集計用のコマンドを書いていてクエリビルダでfrom句にサブクエリを使いたいケースが発生しました。公式ドキュメントを見てもwhere句かjoin句でのサブクエリしか書かれていません。恐らくこうやるのでは？と、試してみたら期待通りに動いてしまい、結局、不安になってソースを確認したのでその共有です。

from句のサブクエリが必要なケースとは？

例えばECサイトにおける顧客分析で注文回数別にユーザ数を算出する場合を考えてみましょう。

以下のような注文テーブルがDBにあったとして、ユーザが商品を注文する度にこのテーブルにレコードが挿入されます。

そして、以下のようなデータを出力したいとします。

１回購入したユーザは60人、２回購入したユーザは50人、３回購入したユーザは10人、、、といった具合です。

これを出力する為のsql文は以下になります。

select
    invoice_count,
    count(user_id) as user_count
from (
    select
        user_id,
        count(invoice_id) as invoice_count
    from invoices
    group by user_id
) as invoices_by_user
group by invoice_count;    

from句にユーザごとの購入回数を取得するサブクエリを指定する必要があり、これをどうやってクエリビルダで組み立てるか？が今回のゴールです。

※次項から解説になりますが、手を動かしながら確認したい方は記事の最後のおまけ、検証データ準備をご参照下さい。

DB::table($subQuery)

冒頭にて触れた通り、公式ドキュメントに載っているのはwhere句やjoin句にsubQueryを指定する方法です。いずれもClosureを指定して実現しています。ということは、table()に同様にClosureを渡せば良いのでは？、と思いやってみました。

まず、$subQueryという変数にClosureを格納します。Closureの引数にクエリビルダを指定し、サブクエリにて実行したいクエリを定義します。

$subQuery = function ($query) {
    $query->from('invoices')
        ->selectRaw('user_id, count(invoice_id) as invoice_count')
        ->groupBy('user_id');
};

次に、それをtable()の引数に指定してみます。

$query = DB::table($subQuery)
    ->selectRaw('invoice_count, count(user_id) as user_count')
    ->groupBy('invoice_count');

最後に、toSql()でクエリを確認してみましょう。（見辛いので改行しています）

>>> $query->toSql();
=> "select invoice_count, count(user_id) as user_count 
    from (
        select user_id, count(invoice_id) as invoice_count 
        from `invoices` 
        group by `user_id`
    ) as `` 
　　　　　　　　group by `invoice_count`"

予想通り(?)、from句のサブクエリを指定することが出来ました。しかし、よく見てみるとサブクエリのエイリアスが空文字(“)になってしまっています。ここで私はこれが正しい使い方なのか不安になり、table()を解析することにしました。

ソース確認

まず、DBファサードを見てみます。すると、Docコメントにてtableメソッドについて記述されています。

namespace Illuminate\Support\Facades;

/**
 * @method static \Illuminate\Database\ConnectionInterface connection(string $name = null)
 * @method static \Illuminate\Database\Query\Builder table(string $table, string $as = null)   <- ココ
...

第二引数にstring $asを取っているので、エイリアスはそこに指定すれば良さそうですね。せっかくなので、メソッドを見てみます。メソッドは下記のどちらかに定義されているようです。

...
 * @see \Illuminate\Database\DatabaseManager
 * @see \Illuminate\Database\Connection
...

確認したところ、\Illuminate\Database\Connectionにありました。

   /**
     * Begin a fluent query against a database table.
     *
     * @param  \Closure|\Illuminate\Database\Query\Builder|string  $table
     * @param  string|null  $as
     * @return \Illuminate\Database\Query\Builder
     */
    public function table($table, $as = null)
    {
        return $this->query()->from($table, $as);
    }

なるほど、第一引数の$tableにはstring以外に、Closureやクエリビルダも渡すことができるみたいです。そして、内部的にはfrom()を呼び出しています。from()はどうなっているのでしょうか？

    /**
     * Set the table which the query is targeting.
     *
     * @param  \Closure|\Illuminate\Database\Query\Builder|string  $table
     * @param  string|null  $as
     * @return $this
     */
    public function from($table, $as = null)
    {
        if ($this->isQueryable($table)) {
            return $this->fromSub($table, $as);
        }

        $this->from = $as ? "{$table} as {$as}" : $table;

        return $this;
    }

なるほどなるほど、$tableがClosureやクエリビルダであればisQueryable()がtrueとなりfromSub()を呼び出すようです。join句にサブクエリを指定するメソッドがjoinSub()なので、from句ならfromSubというわけですね。

まとめ

クエリビルダでサブクエリを指定するには以下のいずれかの方法で実現できます。

// tableメソッドで指定
DB::table($subQuery, $as)->...;

// fromメソッドで指定
DB::query()->from($subQuery, $as)->...;

// fromSubで指定
DB::query()->fromSub($subQuery, $as)->...;

上記の$subQueryは、ClosureでもクエリビルダでもOKです。以下はクエリビルダで指定してみた例です。

$subQuery = DB::table('invoices')
        ->selectRaw('user_id, count(invoice_id) as invoice_count')
        ->groupBy('user_id');

$query = DB::table($subQuery, 'sub')    // エイリアスをsubとしてみました。
    ->selectRaw('invoice_count, count(user_id) as user_count')
    ->groupBy('invoice_count');

$query->toSql();

=> "select invoice_count, count(user_id) as user_count 
    from (
        select user_id, count(invoice_id) as invoice_count 
        from `invoices` group by `user_id`
    ) as `sub`     // as sub になりました。
    group by `invoice_count`"

たまにドキュメントにて網羅されていないメソッドなどがありますが、そんな時は少しソースを覗いてみると良いかもしれませんね。

おまけ、検証データ

実際にデータを入れて手を動かしながら確認してみたい方は以下の手順でデータを用意できます。
Laravel 7.xのインストールにてDBのセットアップまで完了している状態からの手順となります。

Model、migrationファイル、Factoryクラス作成

php artisan make:model Invoice -m -f

invoicesのmigrationファイルを編集します。使用するのはinvoice_idとmember_idのみなので、そちらを追加。

/**
 * Run the migrations.
 *
 * @return void
 */
public function up()
{
    Schema::create('invoices', function (Blueprint $table) {
        $table->id('invoice_id');
        $table->integer('user_id')->default(0);
        $table->timestamps();
    });
}
...

DBに反映。

php artisan migrate

Seederで検証用データを作成。今回はinvoicesテーブルしか使わないので、わざわざ専用のSeederクラスを作成せず、DatabaseSeeder.phpのrun()に直接記述しました。

use App\Invoice;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Seed the application's database.
     *
     * @return void
     */
    public function run()
    {
        $params = [];

        for ($i=0; $i<100; $i++) {

            // user_id毎に 0~10 のランダムな数の注文レコードを作成する
            for ($j=0; $j < rand(0, 10); $j++) { 
                $params[] = [
                    'user_id' => $i,
                ];
            }
        }

        Invoice::insert($params);
    }
}

ユーザ毎に購入回数をばらつかせたいのでrand()を使用して０から１０の注文レコードを、１００ユーザ分用意するようにしました。また、データの挿入が速く終わるように、以前bulk insertで大量のデータをDBに登録するで紹介した方法を採っています。

seederを実行して、DBにレコードを挿入。

php artisan db:seed

mysql上で登録されたデータを確認してみます。
冒頭の、from句のサブクエリが必要なケースとは？で示したクエリを実行してみましょう。

+---------------+------------+
| invoice_count | user_count |
+---------------+------------+
|             1 |         21 |
|             2 |         14 |
|             3 |         21 |
|             4 |         13 |
|             5 |         14 |
|             6 |          4 |
|             7 |          5 |
+---------------+------------+
7 rows in set (0.01 sec)

いい感じにバラけていますね！検証用データの準備は以上です。