Laravel2 Advent Calendar 2019 - Qiita の 21日目の記事です。

時間がない人向け

・whereHasは遅くなるよ。
・MySQL5.6からはIN句が早いからIN句を使ったクエリーで書こう。
・自分のモデルじゃないモデルに条件をつけるのってファサードやnewを許可しちゃうと急にモデルレイヤーが散らかるから、それ用のメソッド作るといいよ。

whereHasは遅い

Laravel EloquentのwhereHasは遅いクエリーになります。
Laravel whereHasで検索すると出てくるEloquentのwhereHasメソッドは辛い（Qiita記事）で述べられているように、EXISTS句を用いたクエリーになってしまうようで、DEPENDENT SUBQUERYが出てしまっているのが原因です。

実際に確かめてみる

データの用意

データはMySQLのSakila-dbを使ってみました。ダウンロードはこちらから

このデータのcustomerテーブルと、paymentテーブルを使ってみます。customerテーブルのデータ件数が５９９件しかないので、１２０万件ほどに増やしてつかってみました。

hasManyコーディング

customer - paymentは1:nの関係になっており、Eloquentで記述するとこんな感じになると思います。

Paymentモデル

Payment.php

<?php
declare(strict_types=1);

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Payment extends Model
{
    protected $table = 'payment';

    protected $primaryKey = 'payment_id';
}

Customerモデル

Customer.php

<?php
declare(strict_types=1);

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Customer extends Model
{
    protected $table = 'customer';

    protected $primaryKey = 'customer_id';

    public function payments(): HasMany
    {
        return $this->hasMany(Payment::class, 'customer_id');
    }

    public function fetch(int $id): ?Customer
    {
        return $this->find($id);
    }

テスト

CustomerTest.php

<?php
declare(strict_types=1);

namespace Tests\Unit;

use App\Models\Customer;
use Tests\TestCase;

class CustomerTest extends TestCase
{
    /**
     * @var Customer
     */
    private $target;

    public function setUp(): void
    {
        parent::setUp();
        $this->target = app()->make(Customer::class);

    }

    public function testFetch()
    {
        $expectId = 1;
        $actual = $this->target->fetch($expectId);

        $this->assertSame($expectId, $actual->customer_id);
        $payments = $actual->payments;
        $this->assertCount(32, $payments);
    }

customer_idが１の人の情報と、支払い履歴一覧を取得する感じでしょうか。

結果

無事取れているようです。

whereHasコーディング

例えば、Aストアで７月に支払いを行ったカスタマー一覧を出したいといった時、自分のモデルcustomerじゃなくてpayment側に条件を付与したいわけです。ここで便利なのがwhereHasになります。
実装してみましょう。

Customerモデル

Customer.php

<?php
declare(strict_types=1);

namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Customer extends Model
{
    protected $table = 'customer';

    protected $primaryKey = 'customer_id';

    public function payments(): HasMany
    {
        return $this->hasMany(Payment::class, 'customer_id');
    }

    public function fetch(int $id): ?Customer
    {
        return $this->find($id);
    }

    public function conditionalFetch()
    {
        return $this
            ->where('store_id', 1)
            ->whereHas('payments', function (Builder $query) {
                $query->whereMonth('payment_date', 7);
            })
            ->get();
    }

テスト

CustomerTest.php

<?php
declare(strict_types=1);

namespace Tests\Unit;

use App\Models\Customer;
use Tests\TestCase;

class CustomerTest extends TestCase
{
    /**
     * @var Customer
     */
    private $target;

    public function setUp(): void
    {
        parent::setUp();
        $this->target = app()->make(Customer::class);

    }

    public function testConditionalFetch()
    {
        $actual = $this->target->conditionalFetch();
        $this->assertCount(326, $actual);
    }

結果

１５秒！！！おっそ！！

何が起こっているか

発行されたクエリーをみてみます。


SELECT 
    *
FROM
    `customer`
WHERE
    `store_id` = 1
        AND EXISTS(
            SELECT 
                *
            FROM
                `payment`
            WHERE
                `customer`.`customer_id` = `payment`.`customer_id`
                    AND MONTH(`payment_date`) = 7
            );

EXISTS句ですね。EXPLAIN結果も

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: customer
   partitions: NULL
         type: ref
possible_keys: idx_fk_store_id
          key: idx_fk_store_id
      key_len: 1
          ref: const
         rows: 610535
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: payment
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 8
          ref: sakila.customer.customer_id
         rows: 26
     filtered: 100.00
        Extra: Using where

ばっちり DEPENDENT SUBQUERYです。

なんとかする

まずSQLをなんとかしてみる

さっきのクエリー、EXISTS句を使わないで書いてみます。

SELECT 
    *
FROM
    `customer`
WHERE
    `store_id` = 1
        AND `customer_id` IN (
        SELECT 
           `customer_id`
        FROM
            `payment`
        WHERE
            MONTH(`payment_date`) = 7
    );

IN句で書くことができます。
MySQL5.6から、このIN句を使ったクエリーパフォーマンスか改善されていると見たことがあるので、EXPLAINしてみます。

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL
possible_keys: idx_fk_customer_id
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 15890
     filtered: 100.00
        Extra: Using where; Start temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: customer
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,idx_fk_store_id
          key: PRIMARY
      key_len: 8
          ref: sakila.payment.customer_id
         rows: 1
     filtered: 50.00
        Extra: Using where; End temporary

DEPENDENT SUBQUERY消えた！！

Eloquentでなんとかする

このSQLをEloquentで記述すると・・・

Customer.php

    public function conditionalFetch(): Collection
    {
        return $this
            ->where('store_id', 1)
            ->whereIn('customer_id', function () {
                // あれ？？paymentのクエリービルダーがないから書けない・・・
            })
            ->get();
    }

PaymentモデルのクエリービルダーがCustomerモデルにはないからIN句の中のクエリーが記述できません。
かといってPayment::みたいにファサード呼び出しするとなんかめちゃめちゃになりそう。

However, some care must be taken when using facades. The primary danger of facades is class scope creep. Since facades are so easy to use and do not require injection, it can be easy to let your classes continue to grow and use many facades in a single class.
Laravel公式でもFacadeのクラス責務の暴走は注意喚起していますね

まぁリレーション＝依存しているのでnewしちゃってもいいんでしょうけど、Model内で他のModelをnewするのって個人的には少し抵抗があります。。。

relationはどうなってんの

っていうか、さっき、Paymentモデル取れたはずです。hasManyでリレーションしたので確実です。
あれを使えないでしょうか。laravelの中を見てみます。

HasRelationship.php

    public function hasMany($related, $foreignKey = null, $localKey = null)
    {
        $instance = $this->newRelatedInstance($related);

        $foreignKey = $foreignKey ?: $this->getForeignKey();

        $localKey = $localKey ?: $this->getKeyName();

        return $this->newHasMany(
            $instance->newQuery(), $this, $instance->getTable().'.'.$foreignKey, $localKey
        );
    }

なるほど、paymentモデルを新しくnewして$instanceに代入。それのnewQuery()でクエリービルダー取得して、hasManyのクエリーにしているようです。フレームワークにいろいろ便利メソッドあるようだぞ。

じゃあ条件付きpaymentモデルを作れるメソッド作ってしまおう

なるべく公式のメソッドを使いながら

    /**
     * Relation先にwhere句を追加したい時用
     * @param string $related
     * @param string|null $foreignKey
     * @return Builder
     */
    public function conditionalRelation(string $related, ?string $foreignKey = null): Builder
    {
        $instance = $this->newRelatedInstance($related);
        if (is_null($foreignKey)) {
            $foreignKey = $instance->getKeyName();
        }

        return $instance->getQuery()->select($foreignKey);
    }

こんなメソッドを作りました。抵抗感じながらも結局newしてるんですけどね（笑）
まぁフレームワークのメソッド使っているので自分的にはセーフです。

基底クラスにでもおいておくといいかもですね。

これで早いクエリーが書ける

Customer.php

    public function conditionalFetch(): Collection
    {
        $paymentBuilder = $this
            ->conditionalRelation(Payment::class, 'customer_id')
            ->whereMonth('payment_date', 7);

        return $this
            ->where('store_id', 1)
            ->whereIn('customer_id', $paymentBuilder)
            ->get();
    }

テスト結果

無事、すんなりテストが通りました。
モデルの表面にはリレーション先のファサードやnewが出てこないので混乱しないし、Eloquentリレーションライクに使えるので抵抗感もないかなーと思います。

Laravel EloquentのwhereHasが遅い問題を何とかする

時間がない人向け

whereHasは遅い

実際に確かめてみる

データの用意

hasManyコーディング

Paymentモデル

Customerモデル

テスト

結果

whereHasコーディング

Customerモデル

テスト

結果

何が起こっているか

なんとかする

まずSQLをなんとかしてみる

Eloquentでなんとかする

relationはどうなってんの

じゃあ条件付きpaymentモデルを作れるメソッド作ってしまおう

これで早いクエリーが書ける