CakePHP3でクロス集計する
この記事は、 CakePHP Advent Calendar 2019 12日目の記事です。
11日目は@kzkamago0721 さんの環境依存な情報を .env にまとめる関連の話でした。
データを2軸で集計して表にする機能を実装することがたまにあります。だいたい時間軸と何かの分類の軸です。この複数軸（だいたい2, 3軸）での集計をクロス集計と呼びますが、いざプログラムを書こうとすると、DBのデータの持ち方とHTMLのtable要素のマークアップとの相性の悪さで辛い思いをしたりします。
今回は、クロス集計した結果をwebページに表示してほしい、そんな機能をCakePHP3でどう実装すればいいか試行錯誤した顛末を書きます。
前提
環境
- PHP 7.4
- CakePHP 3.8
今回のソース一式はGitHubにあります。
デモ用のデータ
デモ用のデータは、accountsテーブルとcategoriesテーブルからなる家計簿にしました。
mysql> SELECT * FROM categories;
+----+--------------------+---------------------+
| id | name | created |
+----+--------------------+---------------------+
| 1 | 住居費 | 2019-12-01 00:00:00 |
| 2 | 水道・光熱費 | 2019-12-01 00:00:00 |
| 3 | 通信費 | 2019-12-01 00:00:00 |
| 4 | 食費 | 2019-12-01 00:00:00 |
+----+--------------------+---------------------+
4 rows in set (0.00 sec)
mysql> SELECT a.id, a.record_date, a.category_id, c.name, a.name, a.amount category_name FROM accounts a JOIN categories c ON a.category_id = c.id;
+----+-------------+-------------+--------------------+--------------+---------------+
| id | record_date | category_id | name | name | category_name |
+----+-------------+-------------+--------------------+--------------+---------------+
| 1 | 2019-09-25 | 1 | 住居費 | 家賃 | 50000 |
| 2 | 2019-10-25 | 1 | 住居費 | 家賃 | 50000 |
| 3 | 2019-11-25 | 1 | 住居費 | 家賃 | 50000 |
| 4 | 2019-12-25 | 1 | 住居費 | 家賃 | 50000 |
| 5 | 2019-09-25 | 2 | 水道・光熱費 | 水道代 | 1414 |
| 6 | 2019-09-25 | 2 | 水道・光熱費 | 電気代 | 2134 |
| 7 | 2019-09-30 | 2 | 水道・光熱費 | ガス代 | 2142 |
| 8 | 2019-10-25 | 2 | 水道・光熱費 | 水道代 | 1821 |
| 9 | 2019-10-25 | 2 | 水道・光熱費 | 電気代 | 2714 |
| 10 | 2019-10-31 | 2 | 水道・光熱費 | ガス代 | 2589 |
| 11 | 2019-11-25 | 2 | 水道・光熱費 | 水道代 | 2121 |
| 12 | 2019-11-25 | 2 | 水道・光熱費 | 電気代 | 2881 |
| 13 | 2019-11-30 | 2 | 水道・光熱費 | ガス代 | 2918 |
| 14 | 2019-12-25 | 2 | 水道・光熱費 | 水道代 | 2383 |
| 15 | 2019-12-25 | 2 | 水道・光熱費 | 電気代 | 3912 |
| 16 | 2019-12-31 | 2 | 水道・光熱費 | ガス代 | 3148 |
| 17 | 2019-09-25 | 3 | 通信費 | 光 | 4500 |
| 18 | 2019-09-25 | 3 | 通信費 | 携帯 | 1980 |
| 19 | 2019-10-25 | 3 | 通信費 | 光 | 4500 |
| 20 | 2019-10-25 | 3 | 通信費 | 携帯 | 1980 |
| 21 | 2019-11-25 | 3 | 通信費 | 光 | 4500 |
| 22 | 2019-11-25 | 3 | 通信費 | 携帯 | 1980 |
| 23 | 2019-12-25 | 3 | 通信費 | 光 | 4500 |
| 24 | 2019-12-25 | 3 | 通信費 | 携帯 | 1980 |
| 25 | 2019-09-07 | 4 | 食費 | スーパー | 4219 |
| 26 | 2019-09-14 | 4 | 食費 | スーパー | 5312 |
| 27 | 2019-09-17 | 4 | 食費 | 外食 | 1200 |
| 28 | 2019-09-19 | 4 | 食費 | 外食 | 900 |
| 29 | 2019-09-21 | 4 | 食費 | スーパー | 3155 |
| 30 | 2019-09-28 | 4 | 食費 | スーパー | 3912 |
| 31 | 2019-10-05 | 4 | 食費 | スーパー | 4517 |
| 32 | 2019-10-12 | 4 | 食費 | スーパー | 5187 |
| 33 | 2019-10-19 | 4 | 食費 | スーパー | 3941 |
| 34 | 2019-10-26 | 4 | 食費 | スーパー | 4126 |
| 35 | 2019-11-02 | 4 | 食費 | スーパー | 3854 |
| 36 | 2019-11-09 | 4 | 食費 | スーパー | 4194 |
| 37 | 2019-11-16 | 4 | 食費 | スーパー | 3343 |
| 38 | 2019-11-23 | 4 | 食費 | スーパー | 4786 |
| 39 | 2019-11-24 | 4 | 食費 | 外食 | 800 |
| 40 | 2019-11-30 | 4 | 食費 | スーパー | 5120 |
| 41 | 2019-12-07 | 4 | 食費 | スーパー | 4258 |
| 42 | 2019-12-09 | 4 | 食費 | 外食 | 800 |
| 43 | 2019-12-14 | 4 | 食費 | スーパー | 3513 |
| 44 | 2019-12-21 | 4 | 食費 | スーパー | 4529 |
+----+-------------+-------------+--------------------+--------------+---------------+
44 rows in set (0.01 sec)
実装する機能
「家計簿のデータをカテゴリーと月の2軸で集計し、表で画面に表示する」という機能をCakePHPで実装していきます。これは、SQLでやるならCASEとGROUP BYを組み合わせて次のように書いたりします。
mysql> SELECT
-> c.name AS category_name,
-> SUM(CASE WHEN a.record_date BETWEEN '2019-09-01' AND '2019-09-30' THEN a.amount ELSE 0 END) AS '2019/09',
-> SUM(CASE WHEN a.record_date BETWEEN '2019-10-01' AND '2019-10-31' THEN a.amount ELSE 0 END) AS '2019/10',
-> SUM(CASE WHEN a.record_date BETWEEN '2019-11-01' AND '2019-11-30' THEN a.amount ELSE 0 END) AS '2019/11',
-> SUM(CASE WHEN a.record_date BETWEEN '2019-12-01' AND '2019-12-31' THEN a.amount ELSE 0 END) AS '2019/12'
-> FROM accounts a
-> JOIN categories c ON a.category_id = c.id
-> GROUP BY c.name;
+--------------------+---------+---------+---------+---------+
| category_name | 2019/09 | 2019/10 | 2019/11 | 2019/12 |
+--------------------+---------+---------+---------+---------+
| 住居費 | 50000 | 50000 | 50000 | 50000 |
| 水道・光熱費 | 5690 | 7124 | 7920 | 9443 |
| 通信費 | 6480 | 6480 | 6480 | 6480 |
| 食費 | 18698 | 17771 | 22097 | 13100 |
+--------------------+---------+---------+---------+---------+
4 rows in set (0.00 sec)
このSQLをクエリビルダーで実現できればPHPやテンプレートで頑張ることなく表を作れそうです。
実装
先にゴールのイメージを。こんな画面を作ります。
URLは/accounts/aggregate?from=2019-09-01&to=2019-12-31こんな感じです。
CASE式
まずCASE式CASE WHEN a.record_date BETWEEN '2019-09-01' AND '2019-09-30' THEN a.amount ELSE 0 ENDをどう組み立てるかを調べていきます。
Cookbookを見ると
値リストよりも case 条件リストの方が少ない場合はいつでも、 addCase は自動的に if .. then .. else 文を作成します。
$query = $cities->find() ->where(function (QueryExpression $exp, Query $q) { return $exp->addCase( [ $q->newExpr()->eq('population', 0), ], ['DESERTED', 'INHABITED'], # 条件に合致したときの値 ['string', 'string'] # それぞれの値の型 ); }); # WHERE CASE # WHEN population = 0 THEN 'DESERTED' ELSE 'INHABITED' END
とあるので、addCaseの第1引数はa.record_date BETWEEN '2019-09-01' AND '2019-09-30'を、第2引数はTHEN、ELSEに対応するaccounts.amountと0を、第3引数には第2引数の型['integer', 'integer']を指定すれば良さそうです。
これを検証するために書いた最初のコードは次の通り。
$q = $this->find();
$case = $q->newExpr()->addCase(
[
$q->newExpr()->between('Accounts.record_date', $m->format('Y-m-01'), $m->format('Y-m-t')),
],
['Accounts.amount', 0],
['integer', 'integer']
);
これはCannot convert value of type `string` to integerというInvalidArgumentExceptionが発生してしまいました。
Cookbookでは参考になる情報を見つけられないのでソースを読んでいくと、Cake\Database\Expression\CaseExpressionの_addExpressionsの中で、第2引数をCake\Database\ExpressionInterfaceのインスタンスに置き換えた上で処理するコードが見つかります。
詳細は省きますが、InvalidArgumentExceptionの原因は、Accounts.amountを式として評価してほしいが実際は単なる文字列として扱われており、かつ型にintegerを指定しているためキャストに失敗した結果だとわかりました。
ExpressionInterfaceというキーワードを得たので、次はExpressionInterfaceのAPIドキュメントを参照します。
いくつかExpressionInterfaceの実装がある中で、IdentifierExpressionの説明を見ると
Represents a single identifier name in the database.
Identifier values are unsafe with user supplied data. Values will be quoted when identifier quoting is enabled.
となっています。これだ！ということで、先ほどのコードを書き換えるとエラーなく想定通りのCASE式が作られるようになりました。
$q = $this->find();
$case = $q->newExpr()->addCase(
[
$q->newExpr()->between('Accounts.record_date', $m->format('Y-m-01'), $m->format('Y-m-t')),
],
[new IdentifierExpression('Accounts.amount'), 0],
['integer', 'integer']
);
その他の部分
CASE式が解決してしまえば、あとはサンプルの多い集約関数の話なのでさらっといきます。
AccountsTableに集計対象の月の範囲を得る関数とクロス集計用のカスタムファインダーを用意します。
カスタムファインダーは、$optionsに対象月の配列を取ることにしました。
/**
* 2つの日付間の月の、月初の日付を配列にして返す。
*
* @param DateTimeInterface $from
* @param DateTimeInterface $to
* @return DateTimeInterface[]
*/
public function getTargetMonths(DateTimeInterface $from, DateTimeInterface $to): array
{
$r = [];
for ($m = $from; $m->format('Y-m-01') <= $to->format('Y-m-01'); $m = $m->modify("+1 months")) {
$r[] = $m;
}
return $r;
}
/**
* クロス集計するカスタムファインダー
*
* @param Query $query
* @param array $options
* @return Query
*/
public function findCrossAggregate(Query $query, array $options): Query
{
$select = [
'Categories.name',
];
/** @var DateTimeInterface[] $months */
$months = $options['months'];
foreach ($months as $m) {
$q = $this->find();
$case = $q->newExpr()->addCase(
[
$q->newExpr()->between('Accounts.record_date', $m->format('Y-m-01'), $m->format('Y-m-t')),
],
[new IdentifierExpression('Accounts.amount'), 0],
['integer', 'integer']
);
$select[$m->format('Y/m')] = $q->func()->sum($case);
}
return $query
->select($select)
->contain(['Categories'])
->group(['Categories.name']);
}
あとはこれをControllerから呼び出すだけです。
public function aggregate()
{
$from = new DateTimeImmutable($this->request->getQuery('from'));
$to = new DateTimeImmutable($this->request->getQuery('to'));
$months = $this->Accounts->getTargetMonths($from, $to);
/** @var \App\Model\Entity\Account[] $aggregatedAccounts */
$aggregatedAccounts = $this->Accounts->find('crossAggregate', ['months' => $months])->toList();
$this->set('accounts', $aggregatedAccounts);
$this->set('months', $months);
}
Viewもこれだけ。
<table cellpadding="0" cellspacing="0">
<thead>
<tr>
<th scope="col">カテゴリー</th>
<?php foreach ($months as $month): ?>
<th scope="col"><?= $month->format('Y/m') ?></th>
<?php endforeach; ?>
</tr>
</thead>
<tbody>
<?php foreach ($accounts as $account): ?>
<tr>
<td><?= h($account->category->name) ?></td>
<?php foreach ($months as $month): ?>
<td><?= $this->Number->format($account[$month->format('Y/m')]) ?></td>
<?php endforeach; ?>
</tr>
<?php endforeach; ?>
</tbody>
</table>
集計も2軸での表現も全部SQLでやってしまっているので、PHPで（クエリビルダー以外）頑張ることなくシンプルに実装できました。
まとめ
CakePHP3でCASE式、事例あるでしょと思って始めたら全然そんなことはなく、ぼちぼちハマりました。。
Cookbookは頼りになるものの、すべての情報を網羅しているわけではないので、APIドキュメントなりソースなりに当たることが大事ですね。
あとは、APIを提供する場合なんかを除いて、スプレッドシートにデータを出力してピボットテーブルを作るのが真っ当なアプローチだと思います。餅は餅屋ですね。