ちょっと便利なSOQL

  • Posted on
  • タグ：
    apexSOQL

はじめに

Force.comではプログラム上からデータベースにアクセスするためのSOQL(Salesforce Object Query Language)という独自のクエリ言語が用意されています。独自言語とは言ってもSQLとほぼ同じ構文なので、今までにSQLを学んだ経験がある人であれば、それほど難しくなく理解できるでしょう。 SOQLのもっとも多い使い方は、Force.comのデータベースであるオブジェクトから、条件を指定してレコードを取得することです。 例えばApex上で、取引先の評価が"見込みあり"になっているレコードを取得するには以下のように書きます。

SOQLではSQLと同じく、集計関数やGROUP BYを使うことも出来ますが、他にも日付の条件として「Force.com組織上で設定した会計年度で取得する」というような、エンタープライズ向けシステムならではの便利な特別値も用意されています。 ということで、今日はSOQLのちょっと便利な使い方を紹介したいと思います。

集計関数

SOQLではSUM()やAVG()などを使って、クエリでデータを集計できます。 例えば受注商談の合計値を取得する場合は以下のように書きます。

ポイントとして、集計関数を含むSOQLの結果はAggregateResult型の配列となります。今回はGROUP BYなどを使用しておらず、結果の配列は1件となるので、results変数の1件目を結果としてデバッグ出力しています。 また、集計関数で別名（summaryの部分）を付けない場合、SOQLが暗黙的に別名expr(i)を付けて返します。iはクエリ内で別名を付けてない集計関数の順番です。1つのクエリ内で別名のないSUM()とAVG()があった場合、1番目はexpr0、2番目はexpr1となりますが、特別な理由がない限り、別名は付けた方が分かりやすいでしょう。

Query Rowsのガバナ制限に注意

集計関数を使う場合に注意したいのが、集計関数を含むクエリにもガバナ制限が適用されることです。これはクエリとして返されるレコード数だけではなく、集計に含まれるレコード数を含みます。 集計関数の利用時に一番引っかかりやすいガバナ制限はQuery Rowsの制限だと思います。投稿日時点でこの制限は50,000件なので、これを越えてしまうことが予想される場合、WHEREで絞込み条件を付けたり、またはサマリー用のオブジェクトを用意するなど、一工夫必要になります。 とは言え、集計関数を使った方が便利な場合もあるので、要件やデータ量を考えた上で、ぜひ利用してみてください。 その他の集計関数についてはこちらをご確認ください。

GROUP BY、HAVING

集計関数はGROUP BYやHAVINGと一緒に利用すると、より様々な集計が出来るようになります。

GROUP BY

GROUP BYは指定した項目の値ごとに集計する場合に使います。 例えば進行中の商談のフェーズ別金額を取得するには以下のように書きます。

デバッグの結果は以下のようになります。（フェーズの名称や金額は組織に依存）

フェーズ=コンタクト：金額=999999
フェーズ=評価：金額=999999
フェーズ=ニーズの把握：金額=999999
フェーズ=デモ/プレゼンテーション：金額=999999
フェーズ=予算/決定者の確認：金額=999999
フェーズ=価格交渉：金額=999999

集計関数だけのSOQLでは、結果となるAggregateResultは1件のみでしたが、GROUP BYを使った場合は指定した項目の値によって複数件となります。 サンプルではループで回して結果を出力しました。

HAVING

HAVINGではGROUP BYでグルーピングした結果に対して、絞込みの条件を入れることが出来ます。絞込み条件という意味ではWHEREと似ていますが、WHEREではグルーピングする前、HAVINGはグルーピングした後です。 次のサンプルは商談のリードソースでグルーピングし、同じ値のレコード数が20以上のリードソース別金額を表示します。

デバッグの結果は以下のようになります。（リードソースの名称や金額は組織に依存）

リードソース=セミナー：金額=999999
リードソース=新聞雑誌広告：金額=999999

日付関数

日付関数はSOQL上で「日付関数(日付項目)」とすることで、その日付関数ごとの値が返ってくる関数です。データを様々な日付期間でグルーピングしたり、フィルタリングする時に便利です。 例えば 「CALENDAR_MONTH(CreatedDate)」は作成日の月の数値を返します。 「FISCAL_QUARTER(CloseDate)」は完了日の会計四半期の数値を返します。 特定年度のレコードだけ取得したい場合などは以下のように書きます。

GROUP BYと併用し、会計年度別の売上を集計することも出来ます。

デバッグの結果は以下のようになります。

年度=2009：金額=999999
年度=2010：金額=999999
年度=2011：金額=999999
年度=2012：金額=999999

日付リテラル

Force.com標準のレポートやビューでお馴染みの「日付の相対値」と同じことが、SOQLでも使用できるのが日付リテラルです。 これは実行日を基準に日付項目に対して「昨日」であったり、「今月」、または「過去90日間」というような指定ができ、条件に該当したレコードを抽出することが出来ます。 例えば"今週"に更新のあったレコードを取得するサンプルは以下のようになります。

日付リテラルはTHIS_WEEKだけではなく、様々なものが用意されています。 詳しくはこちらのリファレンスを参照してください。 ということで、今日はちょっと便利なSOQLをご紹介しました。