はじめに

現在、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] (https://tamulab.jp/mysql-sum-with-ifnull-method/)
　
　　

３　実際の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の方でメソッドを用意。
ネイティブクエリで作成しました。

MoneyRecordRepository.java

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で集計処理を行い、その結果を別エンティティで受け取る
　
　
　

参考