SQL から SOQL への移行
学習の目的
この単元を完了すると、次のことができるようになります。
- Force.com オブジェクトの利点を理解する。
- SQL と SOQL の類似点と相違点を明らかにする。
- ワークベンチを使用して簡単な SOQL ステートメントを作成する。
- より複雑なリレーションクエリを作成する。
- 集計クエリを作成する。
Force.com オブジェクトの理解
Force.com プラットフォームには、アプリケーションをすばやく簡単に作成できるようにする多数の機能を備えた強力なデータベースがあります。SQL Server を操作したことがあれば、データがテーブルや行に保存されることをご存知でしょう。他方、Force.com のデータベースでは、オブジェクトを使用してデータを格納します。オブジェクトは通常のテーブルにある機能をすべて備えているほか、機能性や汎用性を高める拡張機能が追加されています。各オブジェクトは多数の項目で構成され、この項目がデータベースの列に相当します。データはオブジェクトのレコードに格納され、このレコードがデータベースの行に相当します。それだけではありません。
オブジェクトには次の 2 種類があります。
- 標準オブジェクト — Salesforce に組み込まれているオブジェクトです。一般的な CRM オブジェクトには取引先、取引先責任者、商談、リードなどがあります。
- カスタムオブジェクト — アプリケーション固有の情報を格納するために作成する新規オブジェクトです。カスタムオブジェクトは、標準オブジェクトが提供する機能を拡張します。たとえば、商品在庫を追跡するアプリケーションを作成している場合は、Merchandise (商品)、Orders (注文)、Invoices (請求書) といったカスタムオブジェクトを作成できます。
おそらくお察しだと思いますが、オブジェクトには、あるオブジェクトのレコードを別のオブジェクトのレコードにどのように関連付けるかを定義するリレーション項目を設定できます。これらは基本的には主キーおよび外部キーと同じですが、より柔軟であるため、データモデルの設計と実装がより容易になります。
標準オブジェクトでもカスタムオブジェクトでも、Force.com オブジェクトによってデータを格納する構造が決まるだけでなく、ユーザーがインターフェース要素を活用してタブ、ページの項目のレイアウト、関連レコードのリストなどのデータを操作できるようになります。標準機能の場合、ORM を実装したり、データの CRUD のために UI を記述したり、テーブルを作成したりする必要はありません。この標準機能は、プラットフォームによって自動的に提供されます。また、オブジェクトにはアクセス管理、入力規則、数式、履歴管理などの機能に対するサポートも組み込まれています。オブジェクトの属性はすべてメタデータで記述されるため、ビジュアルインターフェースまたはプログラムのいずれかによってレコードを簡単に作成および変更できます。
つまり、オブジェクトはデータを格納する単なるコンテナに留まりません。オブジェクトの豊富な機能によって開発者の手間が大幅に省かれるため、自社アプリケーションに特有の機能の構築に専念できるようになります。カスタムオブジェクト、項目、リレーションなどの作成についての詳細は、「データモデリング」モジュールを参照してください。
似ているが同じではない
.NET 開発者の皆さんは、おそらく SQL Server の操作には精通しているでしょう。そして、SQL を使用してアドホッククエリを記述することにも慣れているでしょう。ですから、SOQL (Salesforce Object Query Language) と呼ばれる Salesforce 専用に設計された類似の言語を紹介するのに最も良い方法は、その両者を比較することだと考えました。
まず知っておくべきなのは、どちらもクエリ言語と呼ばれていますが、SOQL は SELECT ステートメントによるクエリの実行にのみ使用されるということです。SOQL には INSERT、UPDATE、DELETE ステートメントに相当するものがありません。Salesforce では、データ操作には DML (Data Manipulation Language) という方法が使用されます。DML については後で説明します。ここでは、SOQL の SELECT ステートメントを使用して Salesforce データを照会する方法のみを説明します。
すぐに気づく大きな違いは、SOQL には SELECT * などというものは存在しないことです。SOQL は Salesforce データを返し、そのデータはマルチテナント環境に存在しますが、マルチテナント環境では全員が「データベースを共有」しているようなものであるため、* などのワイルドカード文字を使用すると問題が発生します。率直に言うと、特にテーブルの項目名がわからない場合などに、新しい SQL クエリを開始して、SELECT * FROM SOME-TABLE と入力してしまいがちですが、このアクションは、共有環境内の他のテナントに多大な影響を及ぼす可能性があります。それは日曜日の朝 7 時に庭の芝生を刈るようなもので、まったく思いやりのない迷惑な行為です。
SOQL では、返される各項目名を指定します。それ以外は、SOQL の SELECT ステートメントは SQL と似ています。SOQL クエリの記述は簡単だと感じることでしょう。ただし、SQL と SOQL は似ているけれども同じではないということを知っておく必要があります。SOQL は SQL SELECT ステートメントがサポートする高度な機能の一部をサポートしていません。ただし、Salesforce Platform 上では、それらの追加機能は本当に必要ありません。SOQL では、ちょうど必要な機能のみが提供されるため、ユーザーにとって使いやすくなっています。
ワークベンチを使用したクエリの作成
SOQL クエリの記述が話題に上がったところで、どうやって記述するのだろうとお考えかもしれません。クエリの記述を始める簡単な方法は、ワークベンチと呼ばれる Salesforce の Web ベースのツールを使用することです。.NET 開発者たちはツール好きです。この強力なツールを使用すれば、システム管理者や開発者はさまざまな方法で Force.com API を使用して組織にアクセスできます。
ここでは、ワークベンチを使用した SOQL クエリの作成に焦点を絞りますが、時間があればワークベンチを使用してさまざまな機能を確認してみてください。きっと気に入りますよ。無料の Developer Edition (DE) 組織にサインアップし、次の手順を実行します。
- https://workbench.developerforce.com/login.php にアクセスします。
- [Environment (環境)] で [Production (本番)] を選択します。
- [API Version (API バージョン)] ドロップダウンメニューで最新の API バージョンを選択します。
- サービスの利用規約に同意し、[Salesforce でログイン] をクリックします。
- ログイン情報を入力し、[ログイン] をクリックします。
- ワークベンチが自分の情報にアクセスできるようにするには、[Allow (許可)] をクリックします。
- ログイン後に、[queries (クエリ)] > [SOQL Query (SOQL クエリ)] を選択します。
- オブジェクトとして [Account (取引先)] を選択します。
テーブルではなくオブジェクトを選択していることに注目してください。データはオブジェクト内に保持されます。実際、このオブジェクトは Salesforce オブジェクトという意味で sObject と呼ばれ、Force.com プラットフォームと緊密に統合されているため、操作が容易です。
- Ctrl キーを押したまま、項目リストから [CreatedDate (作成日)]、[Name (名前)]、[Phone (電話番号)]、[Type (種別)] を選択します。オブジェクトと項目を選択すると、テキストボックスに SOQL クエリが作成されます。クエリは次のようになります。
SELECT CreatedDate, Name, Phone, Type FROM Account - [Query (クエリ)] をクリックすると、返された結果がリストとして表示されます。結果の中で、CreatedDate 項目に返された値を見てみましょう。
残念なことに、Salesforce の日時項目は、SOQL でも SQL と同様に複雑で操作しにくくなっています。ただし、幸いにも Salesforce には、SOQL での日時項目の操作を容易にするいくつかの日付関数があります。ドキュメントを参照するついでに、SOQL での通貨項目の処理についても確認しておきましょう。通貨項目の処理も多少異なっていて、特に複数の通貨を扱う組織では異なります。
結果の絞り込み
SOQL で必須の句は SELECT と FROM の 2 つのみです。WHERE 句は省略可能です。ただし、良い開発者 (これをお読みの皆さんもそうでしょう) は、記述するほとんどすべてのクエリに WHERE 句を含めようとするものです。必要以上のデータが返されても意味がありませんからね。
ここでも、このしくみを確認する最も簡単な方法はワークベンチを使用することです。
- ワークベンチにログイン後に、[queries (クエリ)] > [SOQL Query (SOQL クエリ)] を選択します。
- オブジェクトとして [Contact (取引先責任者)] を選択します。
- Ctrl キーを押したまま、項目リストから [AccountId (取引先 ID)]、[Email (メール)]、[Id (ID)]、[LastName (姓)] を選択します。オブジェクトと項目を選択すると、テキストボックスに SOQL クエリが作成されます。
-
[Query (クエリ)] をクリックすると、返された結果がリストとして表示されます。クエリは次のようになります。
SELECT AccountId, Email, Id, LastName FROM Contact
このクエリは、組織のすべての取引責任者を返します。開発組織ではリストは短いかもしれませんが、実際の組織では、返される取引先責任者の数は数千にもなる場合があるため、常に WHERE 句を使用して SOQL クエリを絞り込むことを検討する必要があります。Apex コード内で使用する場合は特にそれが重要です。
- [Filter results by (結果の絞り込み条件)] ドロップダウンリストから、[Email (メール)] を選択します。
- Tab キーを押して次の項目に移動し、ドロップダウンボックスの矢印をクリックして、使用できる演算子のリストを表示します。
- リストから [contains (次の文字列を含む)] を選択して、Tab キーを押して最後の項目に移動し、「.net」と入力します。作成されたクエリは次のようになります。
SELECT AccountId, Email, Id, LastName FROM Contact WHERE Email LIKE '%.net%'
どうなったかわかりますか? 作成されたクエリには contains という言葉がありません。その代わりに LIKE が使用されています。さらに、必要な一重引用符 (Email はテキスト項目であるため)、およびワイルドカード検索であることを示す先頭と末尾のパーセント記号が含まれています。
メモ: ワイルドカードを使用すること、特に例のような先頭と末尾のワイルドカードを使用することはお勧めしません。効率的なクエリを作成する方法については後の単元で説明しますが、今のところは、可能な限りこのようなワイルドカード検索を避けるということを覚えておいてください。
- ついでに、[Sort results by (結果の並び替え基準)] ドロップダウンリストで [LastName (姓)] を選択して、結果を並び替えます。
- 他のデフォルトの [A to Z (A から Z)] と [Nulls First (null は先頭)] はそのままにしておきます。クエリは次のようになります。
SELECT AccountId,Email,Id,LastName FROM Contact WHERE Email LIKE '%.net%' ORDER BY LastName ASC NULLS FIRST - [Query (クエリ)] をクリックすると、結果のリストが並び替えられて表示されます。
結果で特に注目してほしいのは、AccountId と Id の 2 つの項目です。これらの項目には、取引先レコードと取引先責任者レコードが作成されたときにプラットフォームによって割り当てられた一意の 18 文字の文字列が含まれています。AccountId 項目は、この特定の取引先責任者が割り当てられている取引先レコードに関連付けられています。SQL 用語では、これは外部キーリレーションです。Id 項目は取引先責任者に関連付けられています。SQL 用語では、これは主キーを表します。
外部キーの話が出たところで、SOQL でテーブルを結合するにはどうするのだろうとお思いかもしれません。一言で答えると、結合しません。SOQL には JOIN 句に相当するものがありません。しかし、心配はいりません。これは良いことなのです。
別の種類の結合
これを聞いても驚かないと思いますが、Salesforce がオブジェクトやテーブルを結合する方法は従来のものとは少し異なります。JOIN 句を使用してテーブルを結合するのではなく、リレーションクエリというものを記述します。
「それで、一体それは何ですか?」という質問が聞こえてきそうですね。よくぞ聞いてくれました。
Salesforce では、親子リレーションを使用して 2 つのオブジェクトを結合します。SQL と同様に SOQL でも外部キーを使用してこれらの 2 つのオブジェクトを関連付けますが、SOQL ではクエリ構文が異なります。正直に言います。この新しい構文では行ではなくオブジェクトを使うため、初めは違和感を覚えるかも知れません。けれども、一旦基本に慣れれば、SQL で結合を記述するよりも、リレーションクエリを記述する方がはるかに簡単だと感じるでしょう。
SOQL では、2 つの基本的なリレーションクエリ種別を覚えておく必要があります。
- 子-親
- 親-子
それぞれ機能が異なります。すでに取引先オブジェクトと取引先責任者オブジェクトのいくつかの項目について取り上げましたが、これらは一般的に結合されるものなので、そこから始めましょう。ここで知っておくべき重要なことは、取引先が親で取引先責任者が子であるということです。
子-親クエリの記述
取引先と取引先責任者の情報を返すクエリを記述するとします。1 つ目のオプションは子-親クエリを記述することです。リレーションクエリは、「ドット表記」を使用して親からのデータにアクセスします。つまり、ピリオドによってリレーション名をクエリ対象の項目名と区別します。
このしくみを理解するために、関連付けられた取引先の名前を含む取引先責任者のリストを返すリレーションクエリの記述を順を追って説明します。ただし、今回は、ワークベンチではなく開発者コンソールの [Query Editor (クエリエディター)] タブを使用します。
-
[設定] をクリックし、[開発者コンソール] をクリックします。 - 開発者コンソールで、下部ペインの [Query Editor (クエリエディター)] タブをクリックします。
- 既存のコードを削除して、次のスニペットを挿入します。
SELECT FirstName, LastName, Account.Name FROM Contact - [実行] をクリックします。
- クエリ結果には 3 つの列が含まれます。結果をスクロールします。Account.name 項目の下のいくつかの結果は null です。これは、すべての取引先責任者が取引先に関連付けられているわけではないためです。
おそらく皆さんはまだ SQL 用語で考えているでしょう。そこで、こちらのシナリオを想像してください。Account と Contact という 2 つの SQL テーブルがあり、テーブル間に一対多リレーションがある場合、同じ情報を返す SQL クエリはどのように記述しますか?
もちろん結合を使用します。ただし、この場合は、取引先に関連付けられていないものも含むすべての取引先責任者を返す上記と同等のクエリを記述するため、右外部結合が必要です。クエリは次のようになります。
SELECT c.FirstName, c.LastName, a.Name FROM Account a
RIGHT JOIN Contact c ON (c.AccountId = a.Id)
ここで前に戻って同等の SOQL を見てみましょう。忘れてしまった場合のために、そのクエリを次に示します。
SELECT FirstName, LastName, Account.Name FROM Contact
SOQL クエリの方がずっと簡単だと思いませんか?
正直なところ、リレーションクエリは扱いが難しい場合があります。特に、カスタムオブジェクトに対するリレーションシップ名が何であるかを把握しにくい場合があります。SQL クエリから SOQL クエリへの変換についての詳細は、こちらのハンズオントレーニング動画を参照してください。
これら種別のクエリについて知っておくべき非常に重要な点は、ドット表記を使用すれば 5 つのレベルをトラバースできることです。そのため、子から親、親の親、親の親の親、というようにたどることができます。
親-子クエリの記述
親-子クエリでもリレーション名が使用されますが、ネストされた選択クエリというものの中で使用されます。前のクエリ種別と同様に、ここでも例を示して説明します。
今回は、Account オブジェクトからクエリを記述し、関連付けられた各取引先責任者の情報を返すネストされたクエリをそこに含めます。
- 開発者コンソールで、下部ペインの [Query Editor (クエリエディター)] タブをクリックします。
- 既存のコードを削除して、次のスニペットを挿入します。
SELECT Name, (Select FirstName, LastName FROM Contacts) FROM Account
ネストされたクエリ内のリレーション名では、Contact ではなく複数形の Contacts が使用されています。これは、つまずきやすい点ですので理解しておくことが重要です。リレーションクエリを使用する場合は、親-子リレーション名は複数形の名前にする必要があります。
カスタムオブジェクトを使用する場合は、リレーション名は複数形にするだけではなく、末尾に 2 つのアンダースコアと r を追加します。たとえば、カスタムオブジェクト My_Object__c のリレーション名は My_Objects__r です。
- [実行] をクリックします。
- クエリ結果には 2 つの列が含まれます。次の図で、Contacts 列の下の結果がどのように表示されているかに注目してください。通常、各取引先は複数の取引先責任者と関連付けられているため、姓と名は JSON 形式で表示されます。
ここでも、その同じクエリが SQL ではどのように記述されるかを見てみましょう。このクエリでは、同等の SQL は次のような左外部結合になります。
SELECT a.Name, c.FirstName, c.LastName
FROM Account a
LEFT JOIN Contact c ON (a.Id = c.AccountId)
この SQL クエリは、すべての取引先レコードとそれらの取引先レコードに関連付けられたすべての取引先責任者を取得します。SQL で返される出力は JSON 形式ではありません。それ以外は、SQL クエリと SOQL クエリの結果は同じになります。
この時点で、SOQL は別名指定をサポートしているだろうかと疑問に思われるかもしれません。サポートしていますが、SQL で使用しているものとは異なります。SOQL には AS キーワードがありません。SOQL では、別名を使用してオブジェクト名を表すことはできますが、項目名の別名指定は集計クエリでのみ使用できます。これについては、次に説明します。
このトピックについてさらに詳しく学習するには、「リソース」セクションのハンズオントレーニング動画のリンクを参照してください。
集計について
SOQL には集計機能があり、ほぼ予想されているとおりに動作します。それでだいたい正解です。集計について注意すべき点は、ほとんどの関数では結果が AggregateResult 型で返されるということです。
SOQL で使用できる関数については、下の表に示します各関数の詳細については公式ドキュメントを参照してください。
SOQL 集計関数
| 関数 | 説明 |
|---|---|
| AVG() | 数値項目の平均値を返します。 |
| COUNT()、COUNT(fieldName)、および COUNT_DISTINCT() | クエリ条件に一致する行数を返します。 |
| MIN() | 項目の最小値を返します。 |
| MAX() | 項目の最大値を返します。 |
| SUM() | 数値項目の合計を返します。 |
SQL で Account という名前の特定のテーブルのレコード件数を取得するクエリは次のようになります。
SELECT COUNT(*) FROM Account
SOQL では同じクエリは次のようになります。
SELECT COUNT() FROM Account
かなり似ていますね。
違いは、どのバージョンの COUNT 関数を使用するかによるものです。バージョンによって戻り値が異なります。項目名がない COUNT() 関数は、他の集計関数より前からある古いバージョンです。整数を返し、SQL の count(*) 関数に最も似ています。
Count(fieldName) は、より新しいバージョンで、fieldName が null 以外の値を持つ行の数を返します。異なる点は、結果を 1 つの値ではなく AggregateResults のリストとして返すということです。
実際に確認してみましょう。
- 開発者コンソールで、下部ペインの [Query Editor (クエリエディター)] タブをクリックします。
- 既存のコードを削除して、次のスニペットを挿入します。
SELECT COUNT() FROM Account - [実行] をクリックします。クエリ結果には、合計行数とその横に数字が表示されます。
- [Query Editor (クエリエディター)] タブに戻って、クエリを次のように変更します。
SELECT COUNT(Id) FROM Account - [実行] をクリックします。今度は、クエリ結果には 1 行のみが返され、レコードの合計数を示す列が表示されます。
これまで SOQL クエリで返されたデータの処理については取り上げていませんでしたが、避けて通るわけにはいきません。少々面倒ですが、集計データの処理に取りかかりましょう。
Apex を少し使用しますが、もし完全に理解できなくても心配はいりません。後ほど詳しく説明します。
- 開発者コンソールで、[Debug (デバッグ)] > [Open Execute Anonymous Window (実行匿名ウィンドウを開く)] を選択します。
- 既存のコードを削除して、次のスニペットを挿入します。
List<AggregateResult> results = [SELECT Industry, count(Id) total FROM Account GROUP BY Industry]; for (AggregateResult ar :results) { System.debug('Industry:' + ar.get('Industry')); System.debug('Total Accounts:' + ar.get('total')); }
GROUP BY 句と共に合計を表す別名が使用されていることに注目してください。SOQL では、GROUP BY 句を使用する集計クエリ内の項目のみ別名を指定できます。
- [Open Log (ログを開く)] が選択されていることを確認し、[Execute (実行)] をクリックします。タブが読み込まれ、実行ログが表示されます。
- ログ内のデバッグステートメントのみが表示されるように、[Debug Only (デバッグのみ)] を選択します。
もうひとこと...
GROUP BY 句以外にも、SOQL には GROUP BY ROLLUP、GROUP BY CUBE、GROUPING などのグループ化句があります。これらの句は、クエリがいくつかの関連テーブルから値を返す場合に、結果を調べるのに便利です。GROUP BY CUBE は、クエリ結果のグループ化項目のすべての組み合わせについて小計を追加する便利な句です。これらの句についての詳細は、「リソース」の GROUP BY についてのドキュメントを参照してください。
集計関数は省略可能な HAVING 句もサポートしています。これは SQL Server の HAVING 句に似ているため、問題なく使用できるでしょう。基本的に、集計関数で返される結果を絞り込むためのものです。詳細は、「リソース」を参照してください。
リソース
- 『Force.com SOQL および SOSL リファレンス』の「SOQL SELECT の構文」
- 『Force.com SOQL および SOSL リファレンス』の「COUNT() および COUNT(fieldName)」
- SOQL クエリと SOSL クエリ
