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モデル
<?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
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
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モデル
<?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
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で記述すると・・・
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の中を見てみます。
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してるんですけどね（笑）
まぁフレームワークのメソッド使っているので自分的にはセーフです。
基底クラスにでもおいておくといいかもですね。
これで早いクエリーが書ける
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リレーションライクに使えるので抵抗感もないかなーと思います。