12月 15, 2022

雄弁なMySQLビュー-stitcher.io

(jp) =

MySQL ビューは、データベースレベルでクエリを格納し、それらを使用して仮想テーブルを生成する方法です。この投稿では、それらを使用する理由と、Eloquent モデルを使用して Laravel に統合する方法について説明します。

MySQL ビューの威力をすでに確信している場合、または単に Laravel でそれらを実装する方法を知りたい場合は、読み飛ばして構いません。

# MySQL ビューの利点

MySQL のビューは、クエリの結果をテーブルのような構造に格納します。通常のテーブルをクエリするのと同じように、このビューをクエリできます。

ビューには次の 2 つの力があります。

結合と共用体を含む複雑なクエリは、単独でクエリ可能なテーブルとして表すことができます。
データのクエリに関しては、MySQL は一般的に私たちよりも賢いです。 PHP でコレクションや配列関数を使用する場合と比較して、パフォーマンスが大幅に向上します。

ただし、ビューの使用には注意点もあります。クエリの種類に応じて、MySQL は実行時にビューを表す「インメモリ」テーブルを作成する必要があります。この操作はテーブルの実体化と呼ばれ、次のような特定のキーワードを使用するときに発生します GROUP BY、または集約関数。

要点は、実行しているクエリの種類によっては、ビューが実際にクエリのパフォーマンスを低下させる可能性があるということです。すべてのものと同様に、ビューは問題によっては良い解決策ですが、他の問題ではひどい考えです。それらを賢く使用し、ここでそれらの制限を読んでください.

# ビューとその代替

与えられた問題をどのように解決できるかを示すために、実際の例を見てみましょう。

私たちはモデルを持っています MeterReading アパートで行われた検針を記録します。建物内のすべてのユニットには、独自の電気、水道、ガスのメーターがあります。

すべての読み取り値は、単位、日付、読み取りを行ったユーザー、タイプ、および実際のメーター値への参照とともにデータベースにリストされます。この例のタイプは electricity、 water また gas.

これは、このテーブルの単純化された移行がどのように見えるかです:

Schema::create('meter_readings', function (Blueprint $table) 
    $table->unsignedInteger('unit_id');
    $table->unsignedInteger('user_id');

    $table->string('type');
    $table->dateTime('date');
    $table->unsignedInteger('value');
);

クライアントから、この生データに基づいてレポートを作成するように依頼されました。彼は、ユニットの概要を確認したいと考えています。各行は、そのユニットのその日の測定値と、すべての測定値が完了したかどうかを表しています。

要するに、彼はこれを見たいと思っています：

+---------+---------+------------+-------------+-------+-----+
| unit_id | user_id | date       | electricity | water | gas |
+---------+---------+------------+-------------+-------+-----+
|      14 |      72 | 2018-08-19 |           0 |     1 |   0 |
|      59 |      61 | 2018-08-06 |           0 |     0 |   1 |
|      41 |      64 | 2018-08-02 |           1 |     1 |   1 |
|      41 |      45 | 2018-08-02 |           1 |     1 |   1 |
...
|      41 |      51 | 2018-08-02 |           1 |     1 |   1 |
+---------+---------+------------+-------------+-------+-----+

レポートには、ユニット、ユーザー、および日ごとにグループ化されたデータセットが表示されます。およびその時点で行われた対応する測定値。

このレポートを生成するいくつかの方法を次に示します。

＃急いで

常にすべてのデータをクエリし、コードでグループ化します。これは最も簡単な方法ですが、いくつかの欠点があります。

PHP および Laravel コレクションは、MySQL が使用できる最適化されたアルゴリズムと比較して低速です。
仮想データセットを構築するということは、ページネーションを手動で実装する必要があることを意味します。 1 つの行で複数のモデルを表すことができます。
読み取り値の特別なコレクションを管理するために、多くのコードを追加しています。

# 生のクエリを使用する

もちろん、PHP をスキップして生のクエリを作成し、MySQL の機能を十分に活用することもできます。これによりパフォーマンスの問題は解決されますが、標準のページネーションを利用できないカスタムデータセットを引き続き使用しています。また、コードのどこかで大きな SQL クエリを維持しています。これはおそらく、PHP のどこかにある文字列か、それより少しましですが、別の sql ファイルです。

# 変更の投影

と呼ばれる別のモデルを作成できます MeterReadingReportで、イベントフックを使用します。 MeterReading これらのレポートを管理します。

測定値が追加されるたびに、そのユニット、日、およびユーザーのレポートを取得または作成できます。それに応じてデータを更新します。

これで、クエリが簡単な別のモデルができました。パフォーマンスへの影響はなくなり、ページネーションの問題も解決されました。

しかし一方で、これらのイベントフックを管理するためのコードはさらに多くあります。レポートの作成は 1 つのことですが、測定値が更新または削除された場合はどうなるでしょうか? これは、私たちが管理しなければならない複雑さです。

ただし、イベントを他のモデルに投影することは悪い考えではありません。これは、イベントソーシングの重要な機能の 1 つです。適切なセットアップがあれば、プロジェクターを作成することは間違いなくオプションです。

この正確なユースケース (laravel-event-projector) を処理するパッケージはありますが、このユースケースにはやり過ぎのように思えました。特に、このプロジェクトには他にも多くの「通常の」モデルがあるためです。

#中間点を見つける

考えられるすべてのソリューションを見て、要件の簡単なリストを作成できます。

コードベースのオーバーヘッドをできるだけ少なくします。
良い成果。
回避策なしで標準の Laravel 機能を使用できる。

MySQL ビューは、この完璧な中間点です。それらがどのように実装されているかを見てみましょう。

# Laravel の SQL ビュー

ビューを操作するには、まずこのビューを構築できるクエリを作成する必要があります。多くの人が SQL を怖がっていますが、最新の ORM はあまりにも怠け者でした。私は SQL がとても楽しいと感じています。

私は SQL の達人ではないので、もっとうまくできることがあるかもしれないことに注意してください。また、ユースケースによって異なるため、このクエリが正確に何をするかについても説明しません。

この場合、上記の表が生成されます。これです：

SELECT 
    unit_id
    , user_id
    , DATE_FORMAT(`date`, '%Y-%m-%d') AS day
    , COUNT(CASE WHEN type = 'electricity' THEN type END) 
        AS `electricity`
    , COUNT(CASE WHEN type = 'water' THEN type END) 
        AS `water`
    , COUNT(CASE WHEN type = 'gas' THEN type END) 
        AS `gas`
    
FROM 
    meter_readings
    
GROUP BY
    unit_id
    , user_id
    , day
;

お気に入りの SQL ブラウザーでこのクエリを作成し、後でプロジェクトにプラグインするのは非常に簡単です。

プラグインする方法、あなたは尋ねますか？非常にシンプルで、移行が含まれています。

public function up()

    DB::statement($this->dropView());
    
    DB::statement($this->createView());

初めに、 dropView Laravel は新しい移行を行うときにのみテーブルを削除するため、必須です。次のように簡単です。

    private function dropView(): string
    
        return <<<SQL
DROP VIEW IF EXISTS `meter_reading_reports`;
SQL;

これらの場合、私は Heredoc を好むことに気付いたでしょう。もちろん、別個の SQL ファイルも同様に優れています。

Michael Dyrynda は、 --drop-views migrate コマンドに渡すことができるフラグ。したがって、技術的には、この手動ドロップは必要ありません。ただし、余分なフラグを追加することを覚えておく必要がないため、私はこの方法を好みます。

次は、 createView メソッドは、いくつかの構文を追加してクエリを返します。サンプルを少し短くしましたが、要点はわかります。

    private function createView(): string
    
        return <<<SQL
CREATE VIEW `meter_reading_reports` AS

SELECT /* … The query */
SQL;

補足: PHP 7.3 と柔軟な Heredoc 構文をとても楽しみにしています。

移行が完了したので、他のすべては通常の Laravel と同じように機能します。

class MeterReadingReport extends Model

    protected $casts = [
        'day' => 'date',
    ];
    
    public function unit(): BelongsTo
    
        return $this->belongsTo(Unit::class);
    

    public function user(): BelongsTo
    
        return $this->belongsTo(User::class);