[Symfony][Doctrine] 論理削除と変更履歴とDBのビューを駆使して複雑な集計ロジックをシンプルなコードで実装した例
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-extensions の SoftDeleteable を使うと楽です。
詳細は以下の公式ドキュメントをご参照いただければと思いますが、
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. 売上 の変更履歴を保持するように
売上 が削除されたときだけでなく、金額 や 担当者 が変更されたときにもその変更内容の履歴が保持される必要があるので、変更履歴を保持できるよう対応が必要です。
変更履歴のデータモデリングについては様々な議論があると思いますが、僕には何も分からないので 今回は 売上 に紐づける形で 売上変更履歴 というエンティティを作ることにします。
// 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;
}
}
こんな感じでエンティティを作ったら、あとは
- 変更前後の
売上エンティティの情報を保持できるイベントクラスを定義 - そのイベントクラスに対応する 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 VIEWとDROP VIEWをup()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、明日も僕です！笑 お楽しみに！
Discussion