拡張SQLについて

こんにちは、CCS田中です。

みなさん、拡張SQL機能をご存知でしょうか？
画面からの設定はできないのですが、JSONファイル等を作成して一覧表示に使ったりすると便利な機能になります。
プリザンターをお使いの方は、ローコードやノーコードのメリットを生かした使い方をされるケースが多いため、拡張SQLで使うクエリーなどの書き方までは分からずに遠ざけてるいるのではないでしょうか？
確かにクエリーを書くためには、SQLの知識が必要ですので少し敷居が高いかもしれません。ただ、それほど複雑なクエリーは書けなくても、このご時世なのでChatGPTに聞いたりすることで難なく作成できたりもすると思います。今回はプリザンターをお使いにユーザが、拡張クエリを使うときにサンプルとなるように例を交えて紹介したいと思います。

自分への備忘録的な意味もあるため、文字だけでわかりづらいことはご了承ください。

拡張クエリのサンプル

今回、拡張クエリだけでなく、拡張フィールドも使ったサンプルとして説明します。

実現したいことは以下の通りです。
「以下の3つのテーブルがあり、商品一覧テーブルの検索条件（フィルタ）で「店舗コード」を指定すると、その店舗で取り扱っている商品一覧が表示される」

１）商品一覧（商品コード[Title]を設定）
２）店舗一覧（店舗コード[Title]と店舗名[分類A]を設定、タイトルは店舗コードと店舗名を接続）
３）店舗取扱商品一覧（商品一覧と店舗一覧へリンク、店舗名[分類A]を選択すると店舗コード[分類C]がルックアップ、商品コード[分類B]を選択すると商品コード[分類D]がルックアップ）
※特に記載のない事項は自由に設定ください。

上記の３）で商品コードが[分類B]にあるのに、[分類D]にルックアップされているか？ということが疑問に思う方がいるかもしれません。これは拡張SQLで商品コードを指定するときに、[分類B]を指定すると、元のテーブルのIDを拾ってきてしまうため、あえて別項目にルックアップで指定しています。この[分類D]は、エディタ画面で非表示に設定しておくとよいと思います。

上記を実現するための対応は以下の手順で行います。

１）商品一覧テーブルに「店舗コード」を検索するための拡張フィールドを設定（JSONファイルを作成）
２）拡張フィールドに設定された内容でフィルタするための拡張クエリを設定（JSONファイルを作成）
３）Webサービスを再起動（Windowsの場合は、IISを再起動）

それでは、詳細を説明していきます。

１）まずは拡張フィールドを設定します。Windowsの場合は以下のフォルダに移動します。
　　パスは環境によって変えてください。
　　C:\web\pleasanter\Implem.Pleasanter\App_Data\Parameters\ExtendedFields

２）extend_shopno.json　というファイル名で、ファイルを作成します。
　中身は以下のように記載してください。下記の　"SiteIdList": [ xxxx ],　の　xxxx部分は、商品一覧テーブルのサイトIDを指定してください。

３）次に拡張SQLを設定します。Windowsの場合は以下のフォルダに移動します。
　　　パスは環境によって変えてください。
　C:\web\pleasanter\Implem.Pleasanter\App_Data\Parameters\ExtendedSqls

４）ShopProd.json　というファイル名で、ファイルを作成します。
中身は以下のように記載してください。下記の　"SiteIdList": [ xxxx ],　の　xxxx部分（2ヶ所）は、商品一覧テーブルのサイトIDを指定してください。
また、zzzz部分は、店舗取扱商品一覧テーブルのサイトIDを指定してください。
下記の"OnSelectingWhereParams" にある、["ShopNo"]は、２）で設定した”Name”部分になります。最後にある「@ShopNo」が、入力された値が代入される箇所になります。
また、今回テーブルは記録テーブルを使用しているため、[Results]となっています。期限付きテーブルを使用している場合は[Issues]としてください。



"CommandText" 部の説明

ここにクエリのWhere文を記載します。
[Results].[SiteId] = xxxx　ここで、SiteIdがxxxxのテーブルに限定します。
[Results].[Title] IN で、この後ろに記載したサブクエリの結果の、Title部分に絞り込みます。サブクエリの結果が複数行のときは、1行ずつ処理されます。
サブクエリ以下は、
select [Results].[ClassD] From [Results] 　では、Resultsテーブルから商品コードが記載されたClassDを返します。
where [Results].[SiteId] = zzzz 　では、SiteIdがzzzzである店舗取扱商品一覧テーブルに絞り込みます。
and [Results].[ClassC] = @ShopNo　では、店舗コード（ClassC）が、拡張フィールドで指定したShopNoを一致したものだけに絞り込みます。

５）上記が設定できたら、Webサービス（Windowsの場合はIIS）を再起動します。
　記載に誤りがあると「アプリケーションエラー」などが発生して表示されませんので、ご注意ください。

ここまでで、設定は完了です。実際に店舗コードで表示されるか確認してみましょう。

まとめ

いかがでしたか？

拡張フィールドや拡張SQLの機能は、若干難しく知識がないと操作できなかったりしますが、上記のサンプルを参考に試してみてください。
この機能を覚えられると、プリザンターの使える幅が広がること間違い無しです！

弊社ではスタイル・スクリプトサンプル集、プリザンター逆引き辞典だけでなく、運用ノウハウ集、Dockerを使った構築の販売もございます。
また、PDF帳票出力支援パックの販売も開始しています。
導入の支援や、スクリプト開発などの支援も行っておりますので、まずは気軽にご相談ください。
※ご相談の内容によっては有料となる場合がございます。予めご了承ください。
☆☆☆
ブログ内容への質問や追加説明をご希望の方は【 問い合わせフォーム 】からお気軽にお問い合わせください
☆☆☆