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

[Laravel2 Advent Calendar 2019 - Qiita](https://qiita.com/advent-calendar/2019/laravel-2) の 21日目 の記事です。

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

# whereHasは遅い
Laravel EloquentのwhereHasは遅いクエリーになります。
Laravel whereHasで検索すると出てくる[EloquentのwhereHasメソッドは辛い（Qiita記事）](https://qiita.com/hisash/items/40532808e61ed3210a6a)で述べられているように、EXISTS句を用いたクエリーになってしまうようで、DEPENDENT SUBQUERYが出てしまっているのが原因です。

# 実際に確かめてみる
### データの用意
データはMySQLの[Sakila-db](https://dev.mysql.com/doc/sakila/en/)を使ってみました。[ダウンロードはこちらから](https://dev.mysql.com/doc/index-other.html)

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

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

#### Paymentモデル
```php: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モデル
```php: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);
    }
```

#### テスト
```php: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が１の人の情報と、支払い履歴一覧を取得する感じでしょうか。
##### 結果
![スクリーンショット 2019-12-17 18.23.43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311914/74ec5007-0155-c7ad-a173-d76d602e2dcb.png)
無事取れているようです。

### whereHasコーディング
例えば、Aストアで７月に支払いを行ったカスタマー一覧を出したいといった時、自分のモデルcustomerじゃなくてpayment側に条件を付与したいわけです。ここで便利なのが[whereHas](https://laravel.com/docs/6.x/eloquent-relationships#querying-relationship-existence)になります。
実装してみましょう。

#### Customerモデル
```php: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();
    }
```

#### テスト
```php: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);
    }
```
##### 結果
![スクリーンショット 2019-12-17 18.47.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311914/d50761ad-de14-cfc8-4086-5d965cca6050.png)
１５秒！！！おっそ！！

### 何が起こっているか
発行されたクエリーをみてみます。

```sql

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結果も

```sql
*************************** 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句を使わないで書いてみます。

```sql
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してみます。

```sql
*************************** 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で記述すると・・・

```php: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のクラス責務の暴走は注意喚起していますね](https://laravel.com/docs/6.x/facades#when-to-use-facades)

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

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

```php: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モデルを作れるメソッド作ってしまおう
なるべく公式のメソッドを使いながら

```php
    /**
     * 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してるんですけどね（笑）
まぁフレームワークのメソッド使っているので自分的にはセーフです。

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

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

```php: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();
    }
```

テスト結果
![スクリーンショット 2019-12-17 20.05.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/311914/5777570d-cb7f-7486-f6c9-a920c5069482.png)

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