実践SOQLで学ぶSalesforceデータベース

by Shinichi Tomita on 7月 12, 2007 at 11:53 午前

Salesforceのデータベースにアクセスするには、SOQLという独自のクエリ言語を用います。この文法自体はSQLによく似ているので、いままでデータベース開発に携わった方であればそれほど難しくないかもしれません。おそらく開発者の方が抱く一番の疑問点は「Salesforceのデータベースのどこにどんなデータが入るのか、そしてどのようにしてそのデータを引っ張ってくるのか」といったところではないかと思います。

Salesforceの標準データモデルについてはこちらのエントリでも簡単に解説しましたが、今回はもう少しユースケースを意識した実践的なデータクエリの例をいくつか挙げてみましょう。

ある取引先に所属している顧客名(取引先責任者)を一覧取得する

SELECT Id, FirstName, LastName, Account.Name
FROM Contact
WHERE Account.Name = '株式会社四川商会'

Contact(取引先責任者)オブジェクトに対しての比較的単純な問い合わせ文ですが、レコードの検索条件を親リレーション(Account)の属性値で指定してフィルタリングしているところが特徴です。

自分(ログインユーザ)が担当している商談と、その商談の更新履歴をすべて取得する

SELECT Id, Name, Account.Name, Amount,
       (SELECT Id, StageName, CreatedDate
        FROM OpportunityHistories
        ORDER BY CreatedDate DESC)
FROM Opportunity
WHERE OwnerId = '{現在ログインしているユーザのID}'

先ほどと同様に2つのオブジェクトにまたがった問い合わせです。前回が子供=>親への参照であったのに対して、今回は親=>子供のレコードへの関連を使用しています。

ここで、副問い合わせの形(括弧で囲まれたクエリ文)で指定されている中にある「OpportunityHistories」とは、Opportunity(商談)オブジェクトからOpportunityHistory (商談履歴) オブジェクトへのリレーションになっています。商談情報のレコードが更新されると自動的にこのオブジェクトに履歴が格納されるようになっています。履歴が時系列に並ぶように子オブジェクトに対してもORDER BY句でレコードを整列させしていることに注目してください。

Opportunity(商談)の問い合わせ条件として、OwnerId(所有者ID)がログインユーザIDと一致しているものに絞り込んでいます。現在ログインしているユーザのIDは getUserInfo APIメソッドで取得することができます。なお、Salesforceにおいてはレコードの所有ユーザは「担当者」という意味でよく使われます。

30日以上活動していない商談を一括取得する

SELECT Id, Name, Account.Name, Amount, LastActivityDate
FROM Opportunity
WHERE LastActivityDate = null
OR LastActivityDate < LAST_N_DAYS:30

検索条件として、LastActivityDate(最終活動日)という項目に対して「過去n日以内」という意味を表す LAST_N_DAYS:n というリテラル記述を使用しています。これにより問い合わせを実行した時点から30日以内に活動がない商談をすべて取得します。

SOQLではこの他にも日付を表すリテラル表記を定義しています。詳細についてはマニュアルをご参照ください

LastActivityDateは、そのレコードに関連付けられているToDoや行動といった活動情報のうち、最後に発生した活動の日時を表示する項目です。これは活動の記録をサポートしているオブジェクトに存在する標準項目です。Opportunity(商談)のほかにもAccount(取引先)やCase(ケース)オブジェクトに存在しています。

ある取引先に対して行った活動履歴、および活動予定を日時が近い順に5件ずつ取得

SELECT Id, Name,
       (SELECT Id, Subject, ActivityDate
        FROM ActivityHistories
        ORDER BY ActivityDate DESC, LastModifiedDate DESC
        LIMIT 5),
       (SELECT Id, Subject, ActivityDate
        FROM OpenActivities
        ORDER BY ActivityDate ASC, LastModifiedDate DESC
        LIMIT 5)
FROM Account
WHERE Id = '{取引先のID}';

Salesforceの標準オブジェクトの中でも「Event(行動)」および「Task(ToDo)」というオブジェクトは、少々特殊な扱いになっています。これらはまとめて「Activity(活動)」という単位で扱われ、さまざまなSalesforceオブジェクトに関連付けて用いることができます。上記のクエリでわかるように、関連付けられた活動情報は、もうすでに完了したものについては「ActivityHistories(活動履歴)」、および未だ完了していないものについては「OpenActivities」という関連をたどることで参照することができます。

なおTask(ToDo)オブジェクトに関してですが、日本語のラベル名は「ToDo」という名前になっているので、このオブジェクトが過去の活動記録の用途にも使われるというのはちょっと混乱してしまうかもしれません。実際の話Salesforceを使う場合に、ToDoを本当にToDoとして用いることはそれほど多くなく、むしろ送信したメールや電話の内容など「すでに行った活動のログ」を保存しておくのにToDoオブジェクトが用いられることのほうが多いくらいです。これは、日本語ラベルではなく英語名の「Task」という名前を考えていただければ、本来このオブジェクトが未来の活動予定のみでなく使われるのは納得いただけるでしょう。

ある部署の今月の売上げを営業マン別に集計して取得

SELECT Amount, Owner.Id, Owner.Name
FROM Opportunity
WHERE IsWon = true
AND CloseDate = THIS_MONTH
AND Owner.UserRole.Name = '営業2課'

これは、よく営業成績のレポートに用いられるタイプのデータクエリだと思います。IsWon(成立フラグ)が商談がまとまったことを示しているため、金額(Amount)を売り上げとしてみなすことができます。検索条件の中のOwnerに指定されているUserRoleという項目は、商談を所有している営業が所属している部署をあらわしています。UserRoleUserオブジェクトに対して1つのみとなり、その意味では「役割」というよりもむしろ「組織階層」を表現するの用いられるので、ちょっと注意が必要かもしれません。

SQLと異なり、現在SOQLでは集計などの集合関数が用意されていないため、そのままではランキングのような形式で利用することができません。そのため、このような場合はクエリを送信したプログラム側で集計作業を行う必要があります。以下にJavaScriptで書いた場合の集計の例を記載します。

var soql = "SELECT Amount, Owner.Id, Owner.Name "+
           " FROM Opportunity "+
           " WHERE IsWon = true "+
           " AND CloseDate = THIS_MONTH "+
           " AND Owner.UserRole.Name = '営業2課'";

var qr = sforce.connection.query(soql);

var tmp = {}; // 集計を行うための一時Hashオブジェクト
var iter = new sforce.QueryResultIterator(qr);
while (iter.hasNext()) {
  var opp = iter.next();
  if (!tmp[opp.Owner.Id]) {
    tmp[opp.Owner.Id] = {
      Name : opp.Owner.Name,
      TotalAmount : opp.Amount ? opp.getInt("Amount") : 0
    };
  } else {
    tmp[opp.Owner.Id].TotalAmount += opp.Amount ? opp.getInt("Amount") : 0;
  }
}
// 配列に変換
var ranking = [];
for (var id in tmp) ranking.push(tmp[id]);
// TotalAmountの降順でソート
ranking = ranking.sort(function(r1, r2) {
  return r1.TotalAmount < r2.TotalAmount ? 1 : -1;
});

なお、これらのクエリを試していただくにしても、Salesforceにデータが入っていなくてはあまり話になりません。実際に運用しているデータがあればベストでしょうが、すべての開発者の方がそのような環境にあるわけではないと思います。そのような方には、テスト用のデータをロードするアプリケーションをこちらから配布しています。ぜひ有効活用してください。

今回はSalesforceの標準オブジェクトのみにフォーカスしてSOQL文の例を挙げました。SOQLは独自に定義したテーブル(カスタムオブジェクト)に対してももちろん使えますが、こちらについては別の機会に詳しく説明できればと思います。

トラックバック

このページのトラックバックURL: http://www.typepad.jp/t/trackback/7240/7046897

このページへのトラックバック一覧 実践SOQLで学ぶSalesforceデータベース:

コメントを投稿

コメントは記事の投稿者が承認するまで表示されません。