はじめに
現在、SpringBootで家計簿アプリを作成しているのですが、Spring Data JPAでの、集計関数使用時のデータ受け渡しが難しかったのでまとめます。
今回やりたいこと
Spring Data JPAで、カテゴリ毎の合計金額を集計し、その結果を取得したい。
歯抜けとは？
例えば、出入金のカテゴリが以下のテーブルに様に設定されていたとする。
＜categoriesテーブル＞
| category_id | category_code(大項目用コード) | subcategory_name |
|---|---|---|
| 101 | 1 | 食費 |
| 102 | 1 | 食料品 |
| 201 | 2 | 日用品 |
| 301 | 3 | 電車 |
| 401 | 4 | 飲み会 |
| 501 | 5 | レジャー |
| 601 | 6 | 新聞 |
| 9901 | 99 | 給与 |
で、以下の様な出入金記録があるとします。
＜moner_recordsテーブル＞
| record_id | category_id | income_and_expense（金額） | record_date（日付） | user_id |
|---|---|---|---|---|
| 5 | 101 | 44444 | 2020-11-12 | xxx |
| 11 | 102 | 2222 | 2020-12-12 | xxx |
| 30 | 9901 | 666666 | 2020-12-16 | xxx |
| 31 | 201 | 4466 | 2020-12-23 | xxx |
category_code 毎の合計金額を算出したい時、ただsum関数を使用しただけだと、下図のように、出入金記録のあるcategory_codeしか表示されない。
| sum(income_and_expense) | category_code |
|---|---|
| 2322 | 1 |
| 4466 | 2 |
| 666666 | 99 |
これが歯抜けの状態です。
本当は、下記のようなデータを取得したい。
| sum(income_and_expense) | category_code |
|---|---|
| 2322 | 1 |
| 4466 | 2 |
| 0 | 3 |
| 0 | 4 |
| 0 | 5 |
| 0 | 6 |
| 666666 | 99 |
今回の問題点
最初かなり悩みましたが、答えは簡単でした。
＜問題点＞
- 出入金テーブルに記録のない（合計が0円の）カテゴリは認識されない。
＜解決策＞
- 記録のないカテゴリも含め、一覧形式で結果を取得したいのなら、別テーブルから情報を持ってくる必要がある。
解決方法
１ 外部結合で、別テーブルからカテゴリ情報を持ってくる。
出入金テーブルとカテゴリテーブルを外部結合して、カテゴリ一覧が取得できるようにする。
※ 外部結合の説明についてはこちら：外部結合を行う(LEFT JOIN句, RIGHT JOIN句
＜left join関数の構文例＞
SELECT * FROM table1
LEFT JOIN table2
ON table1.col1 = table2_col1;
２ ifnull関数で、出入金記録の無いカテゴリの金額を「0」に設定
このままだと、出入金テーブルに記録の無いカテゴリは合計額が「NULL」になってしまうので、ifnull関数で「0」を代入する。
＜ifnull関数の構文例＞
ifnull(値1, 値2)
※ 1 番目の引数の値が NULL だった場合、 2番目の引数の値を返す。
MySQL の IFNULL 関数を SUM 関数と組み合わせるといい感じに集計してくれる | tamulab.jp
３ 実際のSQL文
上記を踏まえた結果、こんな感じのsql文に。
//集計関数にifnull関数を適用
select category_code, ifnull(sum(income_and_expense), 0) from categories C
//left joinでテーブルを結合
//ON句で条件を絞る
left join money_records M on M.user_id = 'xxx'
and M.record_date like '2020-12%' and C.category_id = M.category_id
//category_codeでグループ分け
group by category_code order by category_code asc;
※ 外部結合する際の、ONとWHEREの使い分け
絞込の条件をON句に書くか、WHERE句に書くかで結果が変わってくるらしい。
- ON句に記載すると、結合前のレコードのテーブルに対して、絞り込みをかける。
- WHERE句に記載すると、結合後のテーブルから絞り込む。
今回の場合、WHERE句に条件を書いてしまうと、出入金テーブルに記録のないカテゴリは除外されてしまうので、必ずON句に記載する。
この話の詳細は、このページがわかりやすかったです。
JOIN ON で絞り込み条件を入れるのと、JOIN ONの後WHERE句で絞り込み条件を入れるのとでは、結果が違う件 - なからなLife
これをSpring Data JPAに落とし込む
上記のSQL文を元に、Spring Data JPAの方でメソッドを用意。
ネイティブクエリで作成しました。
public interface MoneyRecordRepository extends JpaRepository<MoneyRecord, Long> {
// 特定の月の、カテゴリー毎の合計を算出
@Query(value = "select category_code, ifnull(sum(income_and_expense), 0) "
+ "from categories C left join money_records M "
+ "on M.user_id = :username and M.record_date like concat(:month, '%') and C.category_id = M.category_id "
+ "group by category_code order by category_code asc", nativeQuery = true)
public List<Object[]> getCategorySummaries(@Param("username") String username, @Param("month") String month);
default List<SummaryByCategory> findCategorySummaries(String username, String month) {
return getCategorySummaries(username, month)
.stream()
.map(SummaryByCategory::new)
.collect(Collectors.toList());
}
}
Spring JPAでネイティブクエリを使用し、別エンティティに結果を代入する方法は、以下のページでまとめています。
【Spring Boot】Spring Data JPAで集計処理を行い、その結果を別エンティティで受け取る