SOQL Tips

## SOQLの構文

```sql:簡易版
SELECT 項目/サブクエリ
FROM オブジェクト名
[WHERE 検索条件]
[GROUP BY 集計単位の項目 [HAVING 集計結果の検索条件]]
[ORDER BY 項目 {ASC|DESC} [NULLS {FIRST|LAST}]]
[LIMIT 検索結果の上限件数]
[OFFSET 無視する検索結果の行数]
```

```sql:完全版
SELECT fieldList [subquery][...]
[TYPEOF typeOfField whenExpression[...] elseExpression END][...]
FROM objectType[,...] 
    [USING SCOPE filterScope]
[WHERE conditionExpression]
[WITH [DATA CATEGORY] filteringExpression]
[GROUP BY {fieldGroupByList|ROLLUP (fieldSubtotalGroupByList)|CUBE (fieldSubtotalGroupByList)} 
    [HAVING havingConditionExpression] ] 
[ORDER BY fieldOrderByList {ASC|DESC} [NULLS {FIRST|LAST}] ]
[LIMIT numberOfRowsToReturn]
[OFFSET numberOfRowsToSkip]
[{FOR VIEW  | FOR REFERENCE} ]
[UPDATE {TRACKING|VIEWSTAT} ]
[FOR UPDATE]
```

- [SOQL SELECT の構文 | SOQL および SOSL リファレンス | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm)

## クエリに関する制限

### SOQLの制限

- WHERE 句の文字列は 4,000 文字を超えることができません。
- 子-親リレーション(`SELECT Account.Name`など)の制限
    - 1回のクエリに指定できる子-親リレーションは、55 個以下です。
    - 指定された各リレーションで、1 つの子-親リレーションに指定できるレベルは 5 つ以下です。たとえば、Contact.Account.Owner.FirstName は 3 レベルです。
- 親-子リレーション（`SELECT (SELECT Id FROM OpportunityLineItems)`など）の制限
    - 1回のクエリに指定できる親-子リレーションは、20 個以下です。
    - REST、SOAPの場合、最大 5 レベルの親-子リレーションを照会できます（v58.0以降）。Big Object、外部オブジェクトとApex、Bulk APIでは、5レベルの親-子リレーションをサポートしていません。

[リレーションクエリ制限について | SOQL および SOSL リファレンス | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_limits.htm)

### トランザクション当たりのApex制限

- SOQLクエリ回数：同期Apex 100、非同期Apex 200
- サブクエリ回数：同期Apex 300、非同期Apex 600[^1]
- SOQLクエリで取得するレコード件数（合計）：50,000

:::note
**Apex制限の留意事項**

- カスタムメタデータのクエリはSOQLクエリ回数にカウントされません。
- サブクエリはSOQLクエリ回数にカウントされません。サブクエリ回数（AggregateQueries）でカウントされます。ただし、WHERE句のサブクエリの回数はカウントされません。
- SELECT句のサブクエリは「SOQLクエリで取得するレコード件数（合計）」にカウントされます。**WHERE句のサブクエリの取得件数はカウントされません。**
- COUNT()、COUNT(fieldname)以外の集計関数を使用すると、集計される対象のレコード数が「SOQLクエリで取得するレコード件数（合計）」にカウントされます。

:::

それぞれの上限、現在値は下記のメソッドで取得できます。

```apex
Limit.getQueries();
Limit.getLimitQueries();

Limit.getQueryRows();
Limit.getLimitQueryRows();

Limit.getAggregateQueries();
Limit.getLimitAggregateQueries();
```

### Salesforce プラットフォームの制限

- ユーザごとに同時に開くクエリカーソルの制限
    - SOAP APIクエリカーソル最大数：10
    - メタデータAPIクエリカーソル最大数：10
    - Apexクエリカーソル最大数：50
    - Apex一括処理startメソッドのクエリカーソル最大数：15
    - Apex一括処理executeメソッドのクエリカーソル最大数：5
    - Apex一括処理finishメソッドのクエリカーソル最大数：5
    - Visualforceカーソル最大数：5
- SOQLクエリの最大実行時間（タイムアウト）：120 秒

:::note
**クエリカーソルについて**
クエリカーソルが最大数に達したとき、古いクエリカーソルから削除されます。queryMore()でクエリカーソルにアクセスします。このとき、クエリカーソルが存在していないと、`INVALID_QUERY_LOCATOR`が発生します。
AttachmentのBodyなどbase64型を含むクエリは1件ごとにqueryMoreが発生します。APIを使用してbase64型を含むクエリを発行して繰り返し処理を実装すると長時間クエリカーソルへのアクセスを行うことになるので、上記のエラーが発生しやすくなります。そのためbase64型項目をクエリする場合は1件ずつクエリを発行することをお勧めします。
:::

文字数

<https://developer.salesforce.com/docs/atlas.ja-jp.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_apexgov.htm>

## SOQLの文法

### SELECT句

#### 重複を除いた件数を取得する

`COUNT_DISTINCT`を使用します。NULLも除外されます。`COUNT`とガバナ制限「SOQLクエリで取得するレコード件数」のカウントの仕方が異なるので注意が必要です。`COUNT_DISTINCT`は集計対象レコードの数がガバナ制限の対象です。

```sql
SELECT COUNT(Id), COUNT_DISTINCT(Product2Id)
FROM OpportunityLineItem
```

- [集計関数 | SOQL および SOSL リファレンス | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm)

### WHERE句

#### 日付型でフィルターする

`yyyy-MM-dd`の形式で比較します。`TODAY`などの日付リテラルを使用できます。

```sql
SELECT Id
FROM Opportunity
WHERE CloseDate > 2022-05-16
```

- [日付形式と日付リテラル | SOQL および SOSL リファレンス | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm)

#### 日時型でフィルターする

`yyyy-MM-ddThh:mm:ss+09:00`の形式で比較します。`+09:00`の箇所はタイムゾーンです。日本時間の時は`+09:00`にします。`TODAY`などの日付リテラルを使用できます。

```sql
SELECT Id
FROM Account
WHERE CreatedDate >= 2022-05-16T00:00:00+09:00 
```

- [日付形式と日付リテラル | SOQL および SOSL リファレンス | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm)

#### 複数選択リストでフィルターする

複数選択リストでフィルターする場合に使用できる演算子は`=`、`!=`、`INCLUDES`、`EXCLUDES`、`;`です。
公式リファレンスには記述がないですが、一応`IN`も使えるようです。`IN`は完全一致になるため誤用に注意です。

- AAAのみ：`Selectable__c = 'AAA'`
- AAAとBBBのみ：`Selectable__c = 'AAA;BBB'`
- AAAを含む：`Selectable__c INCLUDES ('AAA')`
- AAAとBBBを含む：`Selectable__c INCLUDES ('AAA;BBB')`
- AAAとBBBを含む、またはCCCを含む：`Selectable__c INCLUDES ('AAA;BBB', 'CCC')`

Apexで`INCLUDES`にListを使用する場合、バインド変数が使用できないため動的クエリにする必要があります。

```apex
List<String> values = new List<String>{'AAA;BBB', 'CCC'};
String valuesAsString = '(\'' + String.join(values, '\', \'') + '\')';
String query = '';
query += 'SELECT Id FROM Account ';
query += 'WHERE Selectable__c INCLUDES ' + valuesAsString;
List<Account> accList = Database.query(query);
```

- [複数選択リストのクエリ | SOQL および SOSL リファレンス | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_querying_multiselect_picklists.htm)
- [apex - using collections in SOQL INCLUDES - Salesforce Stack Exchange](https://salesforce.stackexchange.com/questions/314035/using-collections-in-soql-includes)

#### あいまい致検索（LIKE句）

部分一致検索には`LIKE`とワイルドカードを使用します。

|ワイルドカード|説明|
|--|--|
|`%`|0文字以上の任意の文字列|
|`_`|任意の1文字|

```sql
SELECT Id
FROM Account
WHERE Name LIKE 'テスト%'
```

あいまい検索の否定で検索する場合、`(NOT FieldName__c LIKE 'word')`のようにします。WHERE句が一つのみであればカッコはなくても問題ないです。

```sql
SELECT Id
FROM Account
WHERE (NOT Name LIKE 'テスト%') AND Type = '顧客' 
```

- [比較演算子 | Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm)

#### 項目の値がリストに含まれているか（IN句）

```sql
SELECT Id, Name
FROM Account
WHERE Name IN ('sForce', 'United Oil & Gas Corp.')
```

```apex
Set<String> names = new Set<String>{'sForce', 'United Oil & Gas Corp.'};
List<Account> accountList = [
    SELECT Id, Name 
    FROM Account 
    WHERE Name IN :names
];
```

- [比較演算子 | Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm)

#### 項目の値がリストに含まれていないか（NOT IN句）

```sql
SELECT Id, Name 
FROM Account 
WHERE Name NOT IN ('sForce', 'United Oil & Gas Corp.')
```

```apex
Set<String> names = new Set<String>{'sForce', 'United Oil & Gas Corp.'};
List<Account> accountList = [
    SELECT Id, Name 
    FROM Account 
    WHERE Name NOT IN :names
];
```

- [比較演算子 | Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm)

#### WHERE句でサブクエリを使用する

`項目 IN (SELECT 比較する項目 FROM ...)`または`項目 NOT IN (SELECT 比較する項目 FROM ...)`のように記述することで、WHERE句でサブクエリを利用できます。
WHERE句でサブクエリを使用する場合、`OR`は使用できません。

```sql
/* OK */
SELECT Name
FROM Account
WHERE Id IN (SELECT AccountId FROM Opportunity WHERE Id = '0065g00000Sr6EwAAJ')

SELECT Phone
FROM Account
WHERE (Phone != null) AND (Id IN (SELECT AccountId FROM Contact WHERE Phone != null))

/* NG: Semi join sub-selects are not allowed with the 'OR' operator */
SELECT Phone
FROM Account
WHERE (Phone != null) OR (Id IN (SELECT AccountId FROM Contact WHERE Phone != null))
```

### ORDER BY句

#### 複数の項目でソートする

`ORDER BY`にカンマ区切りで項目を追加する。

```sql
SELECT Name, Industry
FROM Account
ORDER BY Industry, Id
```

### OFFSET句

#### 結果の一部だけ得る（オフセット）

`LIMIT 取得件数`と`OFFSET スキップする件数`を使用します。
OFFSETは2000まで指定できます。2001以上を指定すると`NUMBER_OUTSIDE_VALID_RANGE`が発生します。
OFFSETを使用するとLIMITに2001以上指定していても2000件までしか取得できません。

```sql
/* 301件目から100件取得する */
SELECT Id
FROM Account
ORDER BY CreatedDate DESC
LIMIT 100
OFFSET 300
```

- [OFFSET | SOQL および SOSL リファレンス | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_offset.htm)

## SOQLをApexで使用する

### 動的にクエリを組み立てる

動的なクエリを文字列で作成し、`Database.query()`に渡すことでクエリを実行することができます。
静的なクエリとことなり、バインド変数に`.`は使用できません。使用した場合、`Variable does not exist`となります。
ユーザ・API・DBから渡された値は、SOQLインジェクションを避けるため`String.escapeSingleQuotes()`でエスケープする必要があります。

```apex
List<String> fields = new List<String>{'Id', 'Name'};
String name = 'Force';

String query = '';
query += 'SELECT ' + String.join(fields, ',') + ' ';
query += 'FROM Account ';
query += 'WHERE Name LIKE \'%' + String.escapeSingleQuotes(name) + '%\'';
List<SObject> fetched = Database.query(query);
```

- [動的 SOQL | Apex 開発者ガイド | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.apexcode.meta/apexcode/apex_dynamic_soql.htm)
- [SOQL インジェクション | Apex 開発者ガイド | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.214.0.apexcode.meta/apexcode/pages_security_tips_soql_injection.htm)

### クエリした結果を変数に代入する

SOQLの結果はSObjectのリストで返ります。１件も取得できなかった場合はnullではなく、空のリストが返却されます。

結果が1件の場合は、SObject型に代入できますが、確実に１件だけ取得できるときのみ使用できます。１件もない場合は`QueryException: List has no rows for assignment to SObject`、結果が複数ある場合は`QueryException: List has more than 1 row for assignment to SObject`が発生します。

```apex
// SObjectのコレクション
List<Account> accList = [SELECT Id FROM Account];
Set<Account> accSet = new Set<Account>([SELECT Id FROM Account]);
Map<Id, Account> accMap  = new Map<Id, Account>([SELECT Id FROM Account]);

// SObject型
Account acc = [SELECT Name FROM Account WHERE Id = :accId LIMIT 1];
```

- [SOQL および SOSL クエリ | Apex 開発者ガイド | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.214.0.apexcode.meta/apexcode/langCon_apex_SOQL.htm)

### 集計関数を使用する

集計関数を使用したSOQLの結果はAggregateResultオブジェクトのリストになります。各項目の値はAggregateResultオブジェクトのgetメソッドで取得します。Apexでも集計関数の別名を使用できます。

```apex:集計関数（別名）
for (AggregateResult ar : [
    SELECT AccountId, COUNT(Id) num, MAX(Amount) max, MIN(Amount) min, AVG(Amount) avg
    FROM Opportunity
    GROUP BY AccountId
]) {
    System.debug(LoggingLevel.DEBUG, ar);
    // AggregateResult:{AccountId=0015h00000SrA4lAAF, num=1, max=235000, min=235000, avg=235000.0}
    System.debug(LoggingLevel.DEBUG, 'accountId:' + ar.get('AccountId'));
    System.debug(LoggingLevel.DEBUG, ' max:' + ar.get('max'));
}
```

別名を使用しない場合、`expr0`などの暗黙的列名が使用されます。

```apex:集計関数（暗黙的列名）
for (AggregateResult ar : [
    SELECT AccountId, COUNT(Id) num, MAX(Amount), MIN(Amount), AVG(Amount)
    FROM Opportunity
    GROUP BY AccountId
]) {
    System.debug(LoggingLevel.DEBUG, ar);
    // AggregateResult:{AccountId=0015h00000SrA4lAAF, num=1, expr0=235000, expr1=235000, expr2=235000.0}
    System.debug(LoggingLevel.DEBUG, 'accountId:' + ar.get('AccountId'));
    System.debug(LoggingLevel.DEBUG, ' max:' + ar.get('expr0'));
}
```

- [SOQL 集計関数の使用 | Apex 開発者ガイド | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.apexcode.meta/apexcode/langCon_apex_SOQL_agg_fns.htm)
- [GROUP BY での別名の使用 | SOQL および SOSL リファレンス | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_groupby_alias.htm)

### ApexでSOQLを使用するときの考慮事項

- クエリは極力セレクティブにします。特にトリガでは非セレクティブなクエリでクエリ対象が200,000件超える場合にはエラーが発生してしまいます。
- クエリで取得したレコードで繰り返し処理を行うときはSOQL For ループを使用します。リストに詰めるよりヒープサイズを削減することができます。
- 大量の子レコード(200件以上)を取得する可能性がある場合は、直接アクセスせずにforループで子レコードを反復処理をします。200件以上の子レコードに直接アクセスするとエラーになります。
- COUNT() および COUNT(fieldname) 以外集計関数を使用するときはガバナ制限（Too many query rows）に注意が必要です。集計に含まれるレコード数もカウントされます。

<https://qiita.com/TaaaZyyy/items/f2de7b92c3b267614304#%E3%82%AF%E3%82%A8%E3%83%AA>

## その他

### クエリのパフォーマンスを測定する

クエリプランツールを使用します。また、REST APIのqueryリソースにexplainパラメータを指定することでもパフォーマンスに関するフィードバックが得られます（ベータ機能）。

- [クエリプランツールの FAQ](https://help.salesforce.com/s/articleView?id=000334796&type=1)
- [クエリのパフォーマンスに関するフィードバックを取得する | REST API 開発者ガイド | Salesforce Developers](https://developer.salesforce.com/docs/atlas.ja-jp.api_rest.meta/api_rest/dome_query_explain.htm#AddingSelectiveFilter)

### クエリにかかる時間を短縮したい

<https://qiita.com/TaaaZyyy/items/be0c95bedfcbb4f05e77>

### Visual Studio Codeでクエリを作成したい

Salesforce公式のVSCode拡張機能に含まれるSOQL Query Builderを使用する。

<https://www.youtube.com/watch?v=t4hBvzyh3SE>

### カスタムメタデータのソート順がおかしい

カスタムメタデータ型のクエリでは、ORDER BYする項目はSELECTしていないとソート順が保証されない、という不具合があります。

- [カスタム メタデータ タイプが並べ替えられない | known issue](https://issues.salesforce.com/issue/a028c00000qQ0UFAA0/custom-metadata-type-not-being-sorted-as-expected)

### カスタムメタデータが取得できない

メタデータリレーション(Entity Definition)、メタデータリレーション(Field Definition)のデータ型のカスタム項目に値が設定されている場合、参照先のオブジェクト・項目への参照権限がないとSOQLでレコードが取得できないようです（Spring'25で確認）。ApexでWITHOUT SHARINGにしている、TOOLING APIやMETADATA APIを使用する、Apexで[カスタムメタデータ型のメソッド](https://developer.salesforce.com/docs/atlas.ja-jp.apexcode.meta/apexcode/apex_methods_system_custom_metadata_types.htm)を使用するとレコードが取得できます。

## 参考

- [SOQL および SOSL リファレンス](https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_sosl_intro.htm)
- [SOQL Basics | Developer Quick Takes - YouTube](https://www.youtube.com/watch?v=muYQ6rRLVsY)
- [Salesforce SOQL: クエリと制限 | セールスフォース・ベン](https://www.salesforceben.com/salesforce-soql-queries-and-limits/)

[^1]:ドキュメントには「親子リレーションのサブクエリを使用する SOQL クエリでは、各親子リレーションは追加クエリとみなされます。これらのクエリタイプは、最上位クエリ数の 3 倍に制限されています。」とありますが、実際は最上位クエリの**制限数**の3倍のようです。