Pleasanter Tips: OnSelectingWhereで複雑な検索条件を設定する。

はじめに

Pleasanterではアクセス権の設定がいろいろな方法で出来ます。また、サーバスクリプトでも検索フィルルターを設定できます。
但し、更に複雑な条件が必要な場合には拡張SQLのOnSelectingWhereでWhere部を設定できるという機能があり、今回はこの機能を紹介してみます。

参考にするPleasanterの情報

拡張SQL
https://pleasanter.org/manual/extended-sql
FAQ：一覧表示するレコードを所属組織別に分けたい
https://pleasanter.org/manual/faq-extended-sql-selecting-where

要件

下記の条件は検索対象になります。

1. 自分が作成したデータ（作成者）の場合
2. 担当者の場合
3. 管理者の場合
4. 作成者と同じ組織に所属していて、ステータスが100と200の場合
5. 閲覧可能部署（ClassD）に所属していて、ステータスが100と200の場合
   閲覧可能部署（ClassD）には[[Deps]]で複数選択を可能にしています。

拡張SQL設定

SiteIdListに関しては環境に合わせて修正してください。
サンプルSQLはSQL-SERVER用になります。

OnSelectingWhereTest.json

{
  "Name": "OnSelectingWhereTest_MSSQL",
  "Description": "OnSelectingWhereテスト",
  "SiteIdList": [999999],
  "OnSelectingWhere": true
}

OnSelectingWhereTest.json.sql

(
  (
    --作成者
    [Results].[Creator]=@_U
  )
  OR (
    --テナント管理者
    (SELECT [TenantManager] FROM [Users] WHERE [UserId]=@_U)=1
  ) 
  OR (
    --担当者
    [Results].[Owner]=@_U 
  )
  OR (
    --管理者
    [Results].[Manager]=@_U
  )
  OR (
    --所属部署
    (SELECT [DeptId] FROM [Users] WHERE [UserId]=[Results].[Owner])=@_D AND [Results].[Status] IN (100,200)
  )
  OR (
    --閲覧可能部署
    @_D IN (SELECT VALUE FROM OPENJSON([Results].[ClassD])) AND [Results].[Status] IN (100,200)
  )
)

ポイント

閲覧可能部署を複数選択にした場合、ClassにはJSON形式で格納されます。従って、JSONをリスト化したデータで検索する必要が有ります。
複数選択にしない場合

CAST(ISNULL([Results].[ClassD],-1) AS INT)=@_D AND [Results].[Status] IN (100,200)

のようになります。
複数選択しない場合、ClassDには単一の組織IDが格納されますが、未選択の場合は空白またはNULLが角のされます。@_D(INT)を直接比較するとエラーになるので、ISNULLとCASTで変換しエラー回避します。

最後に

はじめに書いたとおりに、Pleasanterの設定画面で設定できる方が便利な点が多いです。しかし、ローコード開発においては様々な制限事項も多く、仕様に合わない事も有ります。
Pleasanterではユーザーが設定できる範囲が広く様々な要件に対応できると思います。

注意点

記録テーブルの場合[Results]になりますが、期限付き記録テーブル場合は[Issues]になります。
また、[Results]を指定しなくても動作しますが、使用するフィールドによってはエラーが発生します。
エラーを確認したフィールド[Creator]