[Laravel] Eloquentでのサブクエリの有効な使い方

Eloquentを用いたリレーションテーブルにおいて、効果的なサブクエリの使い方を紹介します。

リレーショナルなテーブル扱う場合にはN+1問題は常に意識されるべきであり、Eager Loadingを使ったクエリの削減は誰もが行なっているでしょう。

Laravelにおいてもwithメソッドを使うことで簡単にEager Loadingが行えますが、1対多 (hasMany)や多対多 (belongsToMany)の関係においてはサブクエリを使うことでパフォーマンスが改善され、システムの見通しがとてもよくなる場合があります。

要件

例として、ユーザーがログインした時にログイン履歴を残すシステムがあり、ユーザー一覧を表示するページで、ユーザー情報に加えて最終ログインデータを表示させるものとします。

Name	Email	LastLogin
Adam Campbell	[email protected]	2018-11-10 12:01
Taylor Otwell	[email protected]	-
Jonathan Reinink	[email protected]	2018-01-02 05:30
Adam Wathan	[email protected]	2018-11-20 07:49

準備

ログイン情報を保存するloginsテーブルと、データベースを操作するLoginモデルを作成しましょう。

php artisan make:model Login -m

以下がマイグレーションするテーブルのスキーマです。

usersテーブルはLarevelに同封されているものを使います。

また、logins.user_idは外部キーでログイン毎に履歴としてレコードが作成されるものとします。

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

Schema::create('logins', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('user_id');
    $table->string('ip_address');
    $table->timestamps();
});

次に、作成したモデルを関連付けます。

class User extends Model
{
    public function logins()
    {
        return $this->hasMany(Login::class);
    }
}

class Login extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

ログイン履歴の追加方法については省略しますが、\Illuminate\Auth\Events\Loginイベントのリスナーとしてテーブルに追加される処理があるものとします。

Eager Loadingを使う方法

ただ単に最終ログインを表示さすことはとても簡単に実装できるでしょう。

$users = User::all();

@foreach ($users as $user)
    <tr>
        <td>{{ $user->name }}</td>
        <td>{{ $user->email }}</td>
        <td>
            @if ($lastLogin = $user->logins()->latest()->first())
                {{ $lastLogin->created_at->format('Y-m-d H:i:s') }}
            @else
                Never
            @endif
        </td>
    </tr>
@endforeach

この方法では50人のユーザーが表示された場合、合計51のクエリが実行されます。いわゆるN+1にあたります。

select * from "users";
select * from "logins" where "logins"."user_id" = 1 and "logins"."user_id" is not null order by "created_at" desc limit 1;
select * from "logins" where "logins"."user_id" = 2 and "logins"."user_id" is not null order by "created_at" desc limit 1;
// ...
select * from "logins" where "logins"."user_id" = 49 and "logins"."user_id" is not null order by "created_at" desc limit 1;
select * from "logins" where "logins"."user_id" = 50 and "logins"."user_id" is not null order by "created_at" desc limit 1;

この問題を解決するにはEager Loadingを使うことです。

関連するログイン情報をwithメソッドを使って取得しましょう。

$users = User::with('logins')->get();

@foreach ($users as $user)
    <tr>
        <td>{{ $user->name }}</td>
        <td>{{ $user->email }}</td>
        <td>
            @if ($user->logins->isNotEmpty())
                {{ $user->logins->sortByDesc('created_at')->first()->created_at->format('Y-m-d H:i:s') }}
            @else
                Never
            @endif
        </td>
    </tr>
@endforeach

これで発行されるクエリはユーザー情報の取得と、それに関連するログイン情報を取得する2つになりN+1問題は解決されました。

しかし、改善前と比べてメモリ消費量が増える場合があります。

ログイン履歴には平均して1ユーザー辺り250件の履歴が存在している場合、50ユーザー分のログイン情報を取得する場合には12,500件のレコードを取得することになります。

これはメモリの消費に加えて、各レコードをEloquentモデルとして初期化する時間もかかることになります。

上記した数字は例ですが、似たような状況で何百万ものレコードがロードされることもあるでしょう。

最終ログインを保持するカラムを用意する

対策の1つとして、専用の情報をデータベース上で保持することが考えられます。

Schema::create('users', function (Blueprint $table) {
   $table->integer('last_login_id');
});

ユーザーのログイン時にログイン履歴に加えて、loginsテーブルの外部キーであるlast_login_idを更新するという方法です。

関連付けを行うlastLoginメソッドをUserモデルに作成し、この情報のEager Loadingを行います。

$users = User::with('lastLogin')->get();

これはとても有効な解決策の1つです。

しかし、キャッシュを頻繁に行うのは簡単ではないことに注意してください。

SubQueryを使う方法

この問題を解決する方法の1つがサブクエリを使う方法です。

サブクエリを使用すると、ユーザー情報の取得を行うクエリの中でログイン情報を取得に関する情報を扱うことができます。

LaravelにはselectSubメソッドを使うことでサブクエリの実行ができます。

$lastLogin = Login::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
    ->limit(1)
    ->getQuery();

$users = User::select('users.*')
    ->selectSub($lastLogin, 'last_login_at')
    ->get();

@foreach ($users as $user)
    <tr>
        <td>{{ $user->name }}</td>
        <td>{{ $user->email }}</td>
        <td>
            @if ($user->last_login_at)
                {{ $user->last_login_at->format('Y-m-d H:i:s') }}
            @else
                Never
            @endif
        </td>
    </tr>
@endforeach

この例ではEager Loadingを使わず、サブクエリを使って各ユーザの最終ログイン日を属性として取得しています。

実際に実行されているデータベースクエリを見てみましょう。

select
    "users".*,
    (
        select "created_at" from "logins"
        where "user_id" = "users"."id"
        order by "created_at" desc
        limit 1
    ) as "last_login_at"
from "users"

このようにサブクエリを使用すると、ユーザーページに必要なすべての情報を単一のクエリで取得できます。

この手法では、データベースクエリとメモリ使用量を最小限に抑えることができ、キャッシングを使わなくて済むので、パフォーマンスが大幅に向上します。

Macroable

このようなサブクエリを使う場合の処理をマクロとして定義することもできます。

新しいaddSubSelectメソッドをクエリビルダに追加するために、AppServiceProviderに次のコードを記述します。

use Illuminate\Database\Query\Builder;

Builder::macro('addSubSelect', function ($column, $query) {
    if (is_null($this->columns)) {
        $this->select($this->from.'.*');
    }

    return $this->selectSub($query->limit(1), $column);
});

このマクロは次の処理が含まれています。

サブクエリに加えて、対象のテーブル情報を取得するためにselect('table.*')を追加しています。これはLaravelでサブクエリを定義した場合にはselect *が含まれないので必須です。
サブクエリは1つのみレコードを取得したいのでlimit(1)を指定します
そして、サブクエリとして引数に渡したクエリを追加します

このマクロを使ってコードを書き直してみましょう。

$users = User::addSubSelect('last_login_at', Login::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
)->get();

とてもスッキリして再利用まで可能になりました。

最後に

この記事は以下のページを参考にサブクエリの使い方について説明しました。

元ページでは更にScopeを使った例や、さらに詳しく説明されていますので是非ご覧になってください。

Dynamic relationships in Laravel using subqueries - Jonathan Reinink