🎻

[Symfony][Doctrine] 論理削除と変更履歴とDBのビューを駆使して複雑な集計ロジックをシンプルなコードで実装した例

2021/12/01に公開約12,700字

Symfony Advent Calendar 2021 の2日目の記事です!🎄🌙

ちなみに、僕はよく TwitterにもSymfonyネタを呟いている ので、よろしければぜひ フォローしてやってください🕊🤲

昨日は @77web さんの 12/9-12/10 Symfony World Online 2021 Winter Editionが開催されます! でした✨

はじめに

少し前にSymfonyで業務システムを作っていてとても設計に悩んだ事案があったのでシェアしたいと思います。

やりたかったこと

まず、前提条件は以下のとおりです。

  • 売上 というエンティティがある
  • 売上金額 計上月 担当者 作成日 というプロパティを持っている
  • 業務においては、売上は 月額制の年間契約 によって発生するため、ユーザーは契約を新規獲得したタイミングで1年分の 売上 をまとめて作成する
  • 契約途中で契約内容が変更されて月額が変わることがあり、この場合ユーザーは変更された月以降の分の 売上金額 をまとめて変更する
  • 契約途中で担当者が変わることがあり、この場合ユーザーは変更された月以降の分の 売上担当者 をまとめて変更する
  • 契約途中で解約となることがあり、この場合ユーザーは解約された月以降の分の 売上 を削除する

この前提において、やりたかったことは

  • 1年分の 売上をまとめて作成する行為は、1年分の「見込売上」を一旦作成しておく行為 と見なせる
  • 同様に、作成済みの 売上 を削除したり、金額 を変更したりする行為は、見込売上を増減させる行為 と見なせる
  • この 「見込売上の増減」の履歴を保持し、担当者 ごとに集計して一覧化 したい
    • 言い換えると、「いつの時点で、誰が、いくらの見込売上を持っていて、それがどのように増減したか」という情報を扱いたい、ということ
  • 担当者 ごとに集計するとなると、売上担当者 が変更された場合、それは 「旧担当者の見込売上が減少し、新担当者の見込売上が増加した」 と見なされなければならない

要件を日本語で説明するだけでも一苦労な内容ですが、ご理解いただけたでしょうか💨

この内容がピンと来ていないとこの先を読んでもよく分からないと思うので、ぜひ何度か読み返してご理解ください🙏

1. 売上 に論理削除を導入

まずは、売上 が削除された場合に「どのような内容の 売上 がいつ削除されたか」という情報が保持される必要があるので、売上 に論理削除を導入します。

Symfony + Doctrineで論理削除を実装する場合、gedmo/doctrine-extensionsSoftDeleteable を使うと楽です。

詳細は以下の公式ドキュメントをご参照いただければと思いますが、

https://github.com/doctrine-extensions/DoctrineExtensions/blob/main/doc/softdeleteable.md
https://github.com/doctrine-extensions/DoctrineExtensions/blob/main/doc/symfony4.md

ざっくり導入手順を殴り書きすると以下のような感じです。

$ composer require gedmo/doctrine-extensions
# config/packages/doctrine.yaml
doctrine:
  orm:
    filters:
      soft_deleteable:
         class: Gedmo\SoftDeleteable\Filter\SoftDeleteableFilter
# config/packages/doctrine_extensions.yaml
services:
  gedmo.listener.softdeleteable:
    class: Gedmo\SoftDeleteable\SoftDeleteableListener
    tags:
      - { name: doctrine.event_subscriber, connection: default }
    calls:
      - [ setAnnotationReader, [ '@annotation_reader' ] ]
// src/Entity/Sale.php

namespace App\Entity;

use Gedmo\Mapping\Annotation as Gedmo;
use Gedmo\SoftDeleteable\Traits\SoftDeleteableEntity;

/**
 * @ORM\Entity(repositoryClass=SaleRepository::class)
 * @Gedmo\SoftDeleteable(fieldName="deletedAt", timeAware=true, hardDelete=true)
 */
class Sale
{
    use SoftDeleteableEntity;
    
    // ...
}
$ bin/console doctrine:migrations:diff

これで、Sale エンティティは

  • 1回目の削除では deletedAt プロパティに削除日時が入るだけで物理削除はされない
  • deletedAt プロパティに削除日時が入っているレコードはデフォルトではDoctrine経由で取得されない
    • ただし $em->getFilters()->disable('soft_deleteable'); のようにフィルターを一時的に解除すれば取得できるようになる(参考
  • deletedAt プロパティに削除日時が入っているレコードをさらに削除すると、物理削除される
  • deletedAt プロパティにNULLをセットし直すと、論理削除を取り消すことができる

という振る舞いを得ます👌

2. 売上 の変更履歴を保持するように

売上 が削除されたときだけでなく、金額担当者 が変更されたときにもその変更内容の履歴が保持される必要があるので、変更履歴を保持できるよう対応が必要です。

変更履歴のデータモデリングについては様々な議論があると思いますが、僕には何も分からないので 今回は 売上 に紐づける形で 売上変更履歴 というエンティティを作ることにします。

参考:変更履歴を持つテーブルの設計 - Qiita

// src/Entity/Sale/History.php

namespace App\Entity\Sale;

use App\Entity\Sale;
use App\Entity\Staff;
use App\Repository\Sale\HistoryRepository;
use Doctrine\ORM\Mapping as ORM;
use Gedmo\Timestampable\Traits\TimestampableEntity;

/**
 * @ORM\Entity(repositoryClass=HistoryRepository::class)
 * @ORM\Table(name="sale_history")
 */
class History
{
    use TimestampableEntity;

    /**
     * @ORM\Id()
     * @ORM\GeneratedValue()
     * @ORM\Column(type="integer")
     */
    private $id;

    /**
     * 売上
     *
     * @ORM\ManyToOne(targetEntity=Sale::class, inversedBy="histories")
     * @ORM\JoinColumn(nullable=false)
     */
    public ?Sale $sale = null;

    /**
     * 担当者(変更前)
     *
     * @ORM\ManyToOne(targetEntity=Staff::class)
     * @ORM\JoinColumn(nullable=false)
     */
    public ?Staff $oldStaff = null;

    /**
     * 担当者(変更後)
     *
     * @ORM\ManyToOne(targetEntity=Staff::class)
     * @ORM\JoinColumn(nullable=false)
     */
    public ?Staff $newStaff = null;

    /**
     * 金額(変更前)
     *
     * @ORM\Column(type="integer")
     */
    public ?int $oldAmount = null;

    /**
     * 金額(変更後)
     *
     * @ORM\Column(type="integer")
     */
    public ?int $newAmount = null;

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

gedmo/doctrine-extensions の Timestampable を使っています。

こんな感じでエンティティを作ったら、あとは

  • 変更前後の 売上 エンティティの情報を保持できるイベントクラスを定義
  • そのイベントクラスに対応する EventSubscriber を定義
  • EventSubscriberから 売上変更履歴 エンティティを作成するように処理を書く
  • 売上 の変更時にコントローラから当該イベントを発行する

のようにすれば対応完了です👌

3. 見込売上の増減をMySQLのビューにしてエンティティにマッピング

さて、ここまでで見込売上を集計するために必要な情報( 売上 の変更履歴と削除履歴)は揃ったので、いよいよ集計ロジックを書いていきたいのですが、いざ取り組んでみると、売上売上履歴 を上手いことJOINしつつ必要な情報だけを切り出して欲しい形に整形するのが信じられないぐらい大変 ということに気づきます😓

そこで今回は、その部分の複雑性をアプリケーション側で担うのではなく、アプリケーション側で最終的に欲しい形の情報をMySQLのビューとして組み上げておいて、そのビューをエンティティにマッピングすることで 複雑性をMySQLのCREATE VIEW文に閉じ込める というアプローチをとってみることにします。

まずは、生SQLをゴリゴリと書いて、

  • 売上 ID
  • 売上変更履歴 ID
  • 担当者 ID
  • 金額 の変化
  • 変化の種別(売上 の作成なのか、売上 の金額変更なのか、売上 の担当者変更なのか、売上の削除なのか)
  • 変化の発生日時

を一覧化するようなビューを作ります。

めちゃめちゃ長いですが、

CREATE VIEW
    sale_change_log (
        id,              -- ID({売上ID}_{売上変更履歴ID}_{種別} 形式の文字列)
        sale_id,         -- 売上ID
        history_id,      -- 売上変更履歴ID
        type,            -- 種別
        occurred_at,     -- 発生日時
        staff_id,        -- 担当者ID
        amount_increase, -- 金額の増加額
        amount_decrease  -- 金額の減少額
    ) AS
SELECT
  *
FROM
  (
    -- 売上作成時の増減情報
    (
      SELECT
        CONCAT(sa.id, '_', '', '_', '売上作成') AS id,
        sa.id AS sale_id,
        NULL AS history_id,
        '売上作成' AS type,
        sa.created_at AS occurred_at,
        IFNULL(h.old_staff_id, sa.staff_id) AS staff_id,
        IFNULL(h.old_amount, sa.amount) AS amount_increase,
        0 AS amount_decrease
      FROM
        sale sa
        -- 最初の変更履歴とだけJOIN
        LEFT JOIN sale_history h ON h.id = (
          SELECT
            h2.id
          FROM
            sale_history h2
          WHERE
            h2.sale_id = sa.id
          ORDER BY
            h2.created_at ASC
          LIMIT
            1
        )
    )
    UNION
    -- 売上削除時の増減情報
    (
      SELECT
        CONCAT(sa.id, '_', '', '_', '売上削除') AS id,
        sa.id AS sale_id,
        NULL AS history_id,
        '売上削除' AS type,
        sa.deleted_at AS occurred_at,
        IFNULL(h.new_staff_id, sa.staff_id) AS staff_id,
        0 AS amount_increase,
        -1 * IFNULL(h.new_amount, sa.amount) AS amount_decrease
      FROM
        sale sa
        -- 最後の変更履歴とだけJOIN
        LEFT JOIN sale_history h ON h.id = (
          SELECT
            h2.id
          FROM
            sale_history h2
          WHERE
            h2.sale_id = sa.id
          ORDER BY
            h2.created_at DESC
          LIMIT
            1
        )
      WHERE
        sa.deleted_at IS NOT NULL
    )
    UNION
    -- 売上の金額変更時の増減情報
    (
      SELECT
        CONCAT(sa.id, '_', h.id, '_', '売上の金額変更') AS id,
        sa.id AS sale_id,
        h.id AS history_id,
        '売上の金額変更' AS type,
        h.created_at AS occurred_at,
        h.new_staff_id AS staff_id,
        GREATEST(0, h.new_amount - h.old_amount) AS amount_increase,
        LEAST(0, h.new_amount - h.old_amount) AS amount_decrease
      FROM
        sale_history h
        LEFT JOIN sale sa ON sa.id = h.sale_id
      WHERE
        h.old_staff_id = h.new_staff_id
    )
    UNION
    -- 売上の担当者変更時の減少情報
    (
      SELECT
        CONCAT(sa.id, '_', h.id, '_', '売上の担当者変更(旧担当者)') AS id,
        sa.id AS sale_id,
        h.id AS history_id,
        '売上の担当者変更(旧担当者)' AS type,
        h.created_at AS occurred_at,
        h.old_staff_id AS staff_id,
        0 AS amount_increase,
        -1 * h.old_amount AS amount_decrease
      FROM
        sale_history h
        LEFT JOIN sale sa ON sa.id = h.sale_id
      WHERE
        h.old_staff_id != h.new_staff_id
    )
    UNION
    -- 売上の担当者変更時の増加情報
    (
      SELECT
        CONCAT(sa.id, '_', h.id, '_', '売上の担当者変更(新担当者)') AS id,
        sa.id AS sale_id,
        h.id AS history_id,
        '売上の担当者変更(新担当者)' AS type,
        h.created_at AS occurred_at,
        h.new_staff_id AS staff_id,
        h.new_amount AS amount_increase,
        0 AS amount_decrease
      FROM
        sale_history h
        LEFT JOIN sale sa ON sa.id = h.sale_id
      WHERE
        h.old_staff_id != h.new_staff_id
    )
  ) AS t
ORDER BY
  t.occurred_at ASC,
  t.sale_id ASC
;

こんな感じで UNION を使って着実に結合していけば、比較的可読的に書くことができます。

エンティティにマッピングする際に @ORM\Id をマークするための値が一意なカラムが必要なので、 {売上ID}_{売上変更履歴ID}_{種別} 形式の文字列を id という名前でカラム化しています。

Doctrine Migrationsを使用している場合でも、以下(展開してご覧ください)のように CREATE VIEWDROP VIEWup() down() に適切に記述してあげることで問題なくマイグレーションが可能です。

表示する

初版の作成

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version2021120200000 extends AbstractMigration
{
    public function up(Schema $schema) : void
    {
        $sql = <<<EOS
DROP VIEW IF EXISTS sale_change_log;
CREATE VIEW (初版の内容);
EOS;

        $this->addSql($sql);
    }

    public function down(Schema $schema) : void
    {
        $this->addSql('DROP VIEW IF EXISTS sale_change_log');
    }
}

第2版へのマイグレーション

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version2021120300000 extends AbstractMigration
{
    public function up(Schema $schema) : void
    {
        $sql = <<<EOS
DROP VIEW IF EXISTS sale_change_log;
CREATE VIEW (第2版の内容);
EOS;

        $this->addSql($sql);
    }

    public function down(Schema $schema) : void
    {
        $sql = <<<EOS
DROP VIEW IF EXISTS sale_change_log;
CREATE VIEW (初版の内容);
EOS;

        $this->addSql($sql);
    }
}

こうして作ったビューを以下のようにエンティティにマッピングします。

<?php

namespace App\Entity\Sale;

use App\Entity\Sale;
use App\Entity\Staff;
use App\Repository\Sale\ChangeLogRepository;
use Doctrine\ORM\Mapping as ORM;

/**
 * 売上増減履歴
 *
 * @ORM\Entity(repositoryClass=ChangeLogRepository::class, readOnly=true)
 * @ORM\Table(name="sale_change_log")
 */
class ChangeLog
{
    /**
     * ID
     *
     * @ORM\Id()
     * @ORM\Column(type="string", length=255)
     *
     * {売上ID}_{売上変更履歴ID}_{種別} 形式の文字列
     */
    public ?string $id = null;

    /**
     * 売上
     *
     * @ORM\ManyToOne(targetEntity=Sale::class)
     */
    public ?Sale $sale = null;

    /**
     * 売上変更履歴
     *
     * @ORM\ManyToOne(targetEntity=History::class)
     */
    public ?History $history = null;

    /**
     * 種別
     *
     * @ORM\Column(type="string", length=255)
     *
     * '売上作成'|'売上削除'|'売上の金額変更'|'売上の担当者変更(旧担当者)'|'売上の担当者変更(新担当者)'
     */
    public ?string $type = null;

    /**
     * 発生日時
     *
     * @ORM\Column(type="datetime")
     */
    public ?\DateTimeInterface $occurredAt = null;

    /**
     * 担当者
     *
     * @ORM\ManyToOne(targetEntity=Staff::class)
     */
    public ?Staff $staff = null;

    /**
     * 金額の増加額
     *
     * @ORM\Column(type="integer")
     */
    public ?int $amountIncrease = null;

    /**
     * 金額の減少額
     *
     * @ORM\Column(type="integer")
     */
    public ?int $amountDecrease = null;
}

これで、ビューで組み立てた一覧表の各レコードをエンティティとして利用できるようになりました👌

4. 「見込売上の増減」エンティティを単純に一覧出力

ここまで来たら、あとは普通に 売上増減履歴 エンティティを一覧で出力するだけで目的を達せられます👌

課題点

生SQLを書くことを許容すると、どうしてもMySQLでしか動かないものになりがちなのが課題です。

今回の例で書いたSQLも、SQLiteで動かないために機能テストにおいてはビューを使わずにフィクスチャから生テーブルを作成してテストする形になりました😓

おわりに

というわけで、Symfony + Doctrineで、論理削除と変更履歴とDBのビューを駆使して複雑な集計ロジックをシンプルなアプリケーションコードで実装した例をご紹介しました。

MySQLにベッタリな実装である点が難点ではありますが、そこ今回の開発では許容できたので目を瞑っています🙏

まったく王道感はないですが、自分なりに色々なパターンを検討した結果、割とメンテナブルな作りにできたかなと思っています。もっといい方法あるよという方は ぜひコメントいただけると嬉しいです 🤲

Symfony Advent Calendar 2021、明日も僕です!笑 お楽しみに!

GitHubで編集を提案

Discussion

ログインするとコメントできます