Nocobaseで運用しているアプリで所属や役割などの履歴を持ちつつ現在の状態を表示させることはできない？_その2
2024.11.07前回試した記事 で、
履歴情報を持ちつつ、現在の状態を判断して表示させる方法を検討していました。
従業員と所属先を関連付けることを例にし、
関連付けの中間テーブルに日付フィールドを設けて判断する方法を取っていましたが、この方法だと表示する際に工夫が必要でした。
関連付けテーブルの内容を制御して表示させる（※3） を使ってデータ範囲を限定して表示させることは可能にはなります。
従業員コレクションから一覧や詳細を表示する際、関連づけられている所属先が全て表示されてしまうのですが、これは制御できませんでした。
今回はコレクションから直接表示させるのではなく、NocobaseのSQLコレクションというのを使って有効なものを表示できないか試してみます。
SQLコレクションとは
SQLコレクションは、SQLステートメントを通してデータを取得する方法を提供します。SQLステートメントを通してデータフィールドを取得し、フィールドメタデータを設定した後、ユーザはそれらをテーブル、チャート、ワークフローなどの通常のテーブルとして使用することができます
コレクションの作成画面から選択して作成できます。
必要な情報をSQLコレクションから取得してみる
作成
では、登録されている従業員と現在有効な所属をSQLコレクションを使って取るようにしてみます。
作成画面にSELECT文を記述して、有効なものであれば保存できます。
実行ボタンを押せばプレビューもできるので、必要なカラムだけにするなど、色々と調整はやりやすいです。
Nocobaseで従業員と所属をManyToManyで関連を作っているので、中間テーブルが作成されています。
この3つのテーブル（従業員、所属、中間テーブル）を結合するSQLを書いて保存しておきます。
WHERE句に必要な条件を指定すれば表示データを絞り込めます。
例えば、
- 現在の日時が配属開始日と配属終了日の期間内か
- 何かしらのフラグがONになっているのか
などですね。
このようにコレクション一覧にSQLコレクションとして表示されます。
表示
ブロックの追加で作成したSQLコレクションを選択します。
このようにSQLで取得できたデータを表示させることはできました。
ただ、中間テーブルに同じ従業員の関連データが複数あるとその分表示されてしまいます。
このように複数のデータをカンマ区切りで表示させるのであれば、SQLを修正する必要がありました。
複数の関連データを１行にするようにSQLを修正
MYSQLをデータソースとして使用しているのですが、GROUP_CONCAT関数を使って複数の所属をカンマ区切りなどで1行にまとめることができました。
SQLの例)
SELECT
e.employee_id,
e.name AS employee_name,
GROUP_CONCAT(d.department_name ORDER BY d.department_name SEPARATOR ', ') AS departments
FROM
employees e
JOIN
employee_department ed ON e.employee_id = ed.employee_id
JOIN
departments d ON ed.department_id = d.department_id
GROUP BY
e.employee_id, e.name;
※クエリの説明
- GROUP_CONCAT(d.department_name ORDER BY d.department_name SEPARATOR ', '):
- GROUP_CONCAT関数を使用して、department_nameをカンマ区切りで1行にまとめます。
- ORDER BYで指定した順序に従って所属先を並べ、SEPARATORで区切り文字を指定しています。
- GROUP BY e.employee_id, e.name:
- employee_idとnameごとに1行にまとめるためのグループ化です。
これで上記のようにカンマ区切りで所属を1つにまとめることができるようになりました。
表示の際の注意点
一般データテーブルやツリーコレクションなどと違い、SQLコレクションでは直接データの編集や削除ができません。
表示だけのために利用する、
関連するデータはリンクを使って従業員や所属のコレクションを表示させる というのが良いと思われます。
