SQL から SOQL への移行

学習の目的

この単元を完了すると、次のことができるようになります。

• Salesforce オブジェクトのメリットを理解する。
  
• SQL と SOQL の類似点と相違点を明らかにする。
  
• ワークベンチを使用して簡単な SOQL ステートメントを作成する。
  
• より複雑なリレーションクエリを作成する。
  
• 集計クエリを作成する。
  

Salesforce オブジェクトを理解する

Salesforce プラットフォームには、アプリケーションをすばやく簡単に作成できるようにする多数の機能を備えた強力なデータベースがあります。SQL Server を操作したことがあれば、データがテーブルや行に保存されることをご存知でしょう。他方、Salesforce のデータベースでは、オブジェクトを使用してデータを格納します。オブジェクトは通常のテーブルにある機能をすべて備えているほか、機能性や汎用性を高める拡張機能が追加されています。各オブジェクトは多数の項目で構成され、この項目がデータベースの列に相当します。データはオブジェクトのレコードに格納され、このレコードがデータベースの行に相当します。それだけではありません。

オブジェクトには次の 2 種類があります。

1. 標準オブジェクト — Salesforce に組み込まれているオブジェクトです。一般的な CRM オブジェクトには取引先、取引先責任者、商談、リードなどがあります。
   
2. カスタムオブジェクト — アプリケーション固有の情報を格納するために作成する新規オブジェクトです。カスタムオブジェクトは、標準オブジェクトが提供する機能を拡張します。たとえば、商品在庫を追跡するアプリケーションを作成している場合は、Merchandise (商品)、Orders (注文)、Invoices (請求書) といったカスタムオブジェクトを作成できます。
   

おそらくお察しだと思いますが、オブジェクトには、あるオブジェクトのレコードを別のオブジェクトのレコードにどのように関連付けるかを定義するリレーション項目を設定できます。これらは基本的には主キーおよび外部キーと同じですが、より柔軟であるため、データモデルの設計と実装がより容易になります。

標準オブジェクトでもカスタムオブジェクトでも、Salesforce オブジェクトによってデータを格納する構造が決まるだけでなく、ユーザーがインターフェース要素を活用してタブ、ページの項目のレイアウト、関連レコードのリストなどのデータを操作できるようになります。標準機能の場合、オブジェクトリレーションマッピング (ORM) を実装したり、データの CRUD のために UI を記述したり、テーブルを作成したりする必要はありません。この標準機能は、プラットフォームによって自動的に提供されます。また、オブジェクトにはアクセス管理、入力規則、数式、履歴管理などの機能に対するサポートも組み込まれています。オブジェクトの属性はすべてメタデータで記述されるため、ビジュアルインターフェースまたはプログラムのいずれかによってレコードを簡単に作成および変更できます。

つまり、オブジェクトはデータを格納する単なるコンテナに留まりません。オブジェクトの豊富な機能によって開発者の手間が大幅に省かれるため、自社アプリケーションに特有の機能の構築に専念できるようになります。カスタムオブジェクト、項目、リレーションなどの作成についての詳細は、「データモデリング」モジュールを参照してください。

似ているが同じではない

.NET 開発者の皆さんは、おそらく SQL Server の操作には精通しているでしょう。そして、SQL を使用してアドホッククエリを記述することにも慣れているでしょう。ですから、SOQL (Salesforce Object Query Language) と呼ばれる Salesforce 専用に設計された類似の言語を紹介するのに最も良い方法は、その両者を比較することだと考えました。

まず知っておくべきなのは、どちらもクエリ言語と呼ばれていますが、SOQL は SELECT ステートメントによるクエリの実行にのみ使用されるということです。SOQL には INSERT、UPDATE、DELETE ステートメントに相当するものがありません。Salesforce では、データ操作にデータ操作言語 (DML) という方法が使用されます。DML については後で説明します。ここでは、SOQL の SELECT ステートメントを使用して Salesforce データを照会する方法のみを説明します。

すぐに気づく大きな違いは、SOQL では SELECT * を実行する方法が異なることです。SOQL は Salesforce データを返し、そのデータはマルチテナント環境に存在しますが、マルチテナント環境では全員が「データベースを共有」しているようなものであるため、* などのワイルドカード文字を使用すると問題が発生します。率直に言うと、特にテーブルの項目名がわからない場合などに、新しい SQL クエリを開始して、SELECT * FROM SOME-TABLE と入力してしまいがちですが、このアクションは、共有環境内の他のテナントに多大な影響を及ぼす可能性があります。テナント構造を保護し、項目レベルセキュリティを尊重するために、SOQL では FIELDS() を使用して、アクセス権限がある項目のみを表示します。たとえば、SELECT FIELDS(ALL) FROM Account は、取引先オブジェクトに対してアクセス権があるすべての標準項目とカスタム項目を返します。

SOQL では、返される各項目名を指定します。それ以外は、SOQL の SELECT ステートメントは SQL と似ています。SOQL クエリの記述は簡単だと感じることでしょう。ただし、SQL と SOQL は似ているけれども同じではないということを知っておく必要があります。SOQL は SQL SELECT ステートメントがサポートする高度な機能の一部をサポートしていません。ただし、Salesforce Platform 上では、それらの追加機能は本当に必要ありません。SOQL では、ちょうど必要な機能のみが提供されるため、ユーザーにとって使いやすくなっています。

開発者コンソールでクエリを作成する

SOQL クエリの記述が話題に上がったところで、どうやって記述するのだろうとお考えかもしれません。1 つの方法は、開発者コンソールを使用することです。.NET 開発者たちはツール好きです。この強力なツールを使用すれば、システム管理者や開発者がさまざまな方法で Salesforce API を使用して組織にアクセスできます。.NET 開発者の方は、おそらく Visual Studio コードのことは熟知されていると思います。実はローカルマシンでカスタム開発や SOQL の作成を行うことができる VS Code 向け Salesforce 拡張機能が用意されています。この拡張機能は、最新のソース駆動型開発体験を提供する Salesforce DX と密接に結び付いています。Salesforce DX の詳細は「Salesforce DX 入門」トレイルを参照してください。

ここでは、開発者コンソールを使用した SOQL クエリの作成に焦点を絞りますが、時間があれば開発者コンソールを使用してさまざまな機能を確認してみてください。きっと気に入りますよ。無料の Developer Edition (DE) 組織にサインアップし、次の手順を実行します。

1. [Setup (設定)] () メニューから、[Developer Console (開発者コンソール)] を選択して開発者コンソールを開きます。
   
2. 下部ペインの [Query Editor (クエリエディター)] タブをクリックします。
   
   

3. 次のクエリを入力します。
   SELECT Id, Name, Type FROM Account

4. [Execute (実行)] をクリックします。
   クエリ結果には 3 つの列が含まれます。結果をスクロールします。
   

クエリは、テーブルではなく、オブジェクトから項目を取得します。データはオブジェクト内に保持されます。実際、このオブジェクトは Salesforce オブジェクトという意味で sObject と呼ばれ、Salesforce プラットフォームに緊密に統合されているため、操作が容易です。

残念なことに、Salesforce の日時項目は、SOQL でも SQL と同様に複雑で操作しにくくなっています。ただし、幸いにも Salesforce には、SOQL での日時項目の操作を容易にするいくつかの日付関数があります。ドキュメントを参照するついでに、SOQL での通貨項目の処理についても確認しておきましょう。通貨項目の処理も多少異なっていて、特に複数の通貨を扱う組織では異なります。

結果の絞り込み

SOQL で必須の句は SELECT と FROM の 2 つのみです。WHERE 句は省略可能です。ただし、良い開発者 (これをお読みの皆さんもそうでしょう) は、記述するほとんどすべてのクエリに WHERE 句を含めようとするものです。必要以上のデータが返されても意味がありませんからね。

ここでも、このしくみを確認する最も簡単な方法はワークベンチを使用することです。

1. [Setup (設定)] () メニューから、[Developer Console (開発者コンソール)] を選択して開発者コンソールを開きます。
   
2. 下部ペインの [Query Editor (クエリエディター)] タブをクリックします。
   
3. 次のクエリを入力します。
   SELECT AccountId, Email, Id, LastName FROM Contact

4. [Execute (実行)] をクリックします。
   

このクエリは、組織のすべての取引責任者を返します。開発組織ではリストは短いかもしれませんが、実際の組織では、返される取引先責任者の数は数千にもなる場合があるため、常に WHERE 句を使用して SOQL クエリを絞り込むことを検討する必要があります。Apex コード内で使用する場合は特にそれが重要です。

1. Where 句を追加して、クエリを更新します。
   SELECT AccountId, Email, Id, LastName FROM Contact WHERE Email LIKE '%.net%'
   

2. [Execute (実行)] をクリックします。
   

どうなったかわかりますか? 作成されたクエリには contains という言葉がありません。その代わりに LIKE が使用されています。さらに、必要な一重引用符 (Email はテキスト項目であるため)、およびワイルドカード検索であることを示す先頭と末尾のパーセント記号が含まれています。

メモ: ワイルドカードを使用すること、特に例のような先頭と末尾のワイルドカードを使用することはお勧めしません。効率的なクエリを作成する方法については後の単元で説明しますが、今のところは、可能な限りこのようなワイルドカード検索を避けるということを覚えておいてください。

1. ついでに、ORDR BY 句を使用して結果を LastName で並べ替えます。クエリは次のようになります。
   SELECT AccountId, Email, Id, LastName FROM ContactWHERE Email LIKE '%.net%' ORDER BY LastName ASC NULLS FIRST

2. [Execute (実行)] をクリックすると、結果の順序付きリストが表示されます。
   

結果で特に注目して欲しいのは、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 (クエリエディター)] タブを使用します。

1. 開発者コンソールで、下部ペインの [Query Editor (クエリエディター)] タブをクリックします。
   
2. 次のクエリを入力します。
   SELECT FirstName, LastName, Account.Name FROM Contact

3. [Execute (実行)] をクリックします。
   

おそらく皆さんはまだ 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 オブジェクトからクエリを記述し、関連付けられた各取引先責任者の情報を返すネストされたクエリをそこに含めます。

1. 開発者コンソールで、下部ペインの [Query Editor (クエリエディター)] タブをクリックします。
   
2. 次のクエリを入力します。
   SELECT Name, (Select FirstName, LastName FROM Contacts) FROM Account
   
   ネストされたクエリ内のリレーション名では、Contact ではなく複数形の Contacts が使用されています。これは、つまずきやすい点ですので理解しておくことが重要です。リレーションクエリを使用する場合は、親-子リレーション名は複数形の名前にする必要があります。
   
   カスタムオブジェクトを使用する場合は、リレーション名は複数形にするだけではなく、末尾に 2 つのアンダースコアと r を追加します。たとえば、カスタムオブジェクト My_Object__c のリレーション名は My_Objects__r です。
   

3. [Execute (実行)] をクリックします。
   クエリ結果には 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 集計関数

SQL で Account という名前の特定のテーブルのレコード件数を取得するクエリは次のようになります。

SELECT COUNT(*) FROM Account

SOQL では同じクエリは次のようになります。

SELECT COUNT() FROM Account

かなり似ていますね。

違いは、どのバージョンの COUNT 関数を使用するかによるものです。バージョンによって戻り値が異なります。項目名がない COUNT 関数は、ほかの集計関数より前からある古いバージョンです。整数を返し、SQL の count(*) 関数に最も似ています。

Count(fieldName) は、より新しいバージョンで、fieldName が null 以外の値を持つ行の数を返します。異なる点は、結果を 1 つの値ではなく AggregateResults のリストとして返すということです。

実際に確認してみましょう。

1. 開発者コンソールで [Query Editor (クエリエディター)] をクリックします。
   
2. 次のクエリを入力します。
   SELECT COUNT() FROM Account

3. [Execute (実行)] をクリックします。
   クエリ結果には、合計行数とその横に数字が表示されます。
   
4. [Query Editor (クエリエディター)] タブに戻り、クエリを次のように変更します。
   SELECT COUNT(Id) FROM Account

5. [実行] をクリックします。
   今度は、クエリ結果には 1 行のみが返され、レコードの合計数を示す列が表示されます。
   

これまで SOQL クエリで返されたデータの処理については取り上げていませんでしたが、避けて通るわけにはいきません。少々面倒ですが、集計データの処理に取りかかりましょう。

Apex を少し使用しますが、完全に理解できなくても心配はいりません。後ほど詳しく説明します。

1. 開発者コンソールで、[Debug (デバッグ)] を選択し、[Open Execute Anonymous Window (実行匿名ウィンドウを開く)] を選択します。
   
2. 既存のコードを削除し、次のスニペットを挿入します。
   

   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 句を使用する集計クエリ内の項目のみ別名を指定できます。
   

3. [Open Log (ログを開く)] が選択されていることを確認し、[Execute (実行)] をクリックします。
   タブが読み込まれ、実行ログが表示されます。
   
4. ログ内のデバッグステートメントのみが表示されるように、[Debug Only (デバッグのみ)] を選択します。
   

もうひとこと...

GROUP BY 句以外にも、SOQL には GROUP BY ROLLUP、GROUP BY CUBE、GROUPING などのグループ化句があります。これらの句は、クエリがいくつかの関連テーブルから値を返す場合に、結果を調べるのに便利です。GROUP BY CUBE は、クエリ結果のグループ化項目のすべての組み合わせについて小計を追加する便利な句です。これらの句についての詳細は、「リソース」の GROUP BY についてのドキュメントを参照してください。

集計関数は省略可能な HAVING 句もサポートしています。これは SQL Server の HAVING 句に似ているため、問題なく使用できるでしょう。基本的に、集計関数で返される結果を絞り込むためのものです。詳細は、「リソース」を参照してください。

リソース

• SOQL および SOSL リファレンス: SOQL SELECT の構文
• SOQL および SOSL リファレンス: Count および Count(fieldName)
• Apex 開発者ガイド: SOQL クエリと SOSL クエリ
• SOQL および SOSL リファレンス: GROUP BY