Help us understand the problem. What is going on with this article?

SOQLのチートシート

More than 1 year has passed since last update.

SOQLのチートシート

SOQLでよく使うやつの覚書。
またSQLでは普通にできるけどSOQLでできないことも記載。

SOQL

全件表示

SELECT Id,Name,LastModifiedDate
FROM Account
ORDER BY LastModifiedDate desc

5件だけ表示(Limit)

SELECT Id,Name,LastModifiedDate
FROM Account
LIMIT 5 

5件だけ表示(Offset)

SELECT Id,Name,LastModifiedDate
FROM Account
OFFSET  5 

完全一致検索(=)

SELECT Id,Name,LastModifiedDate
FROM Account
WHERE name = '田中太郎' Order by Name

あいまい検索(like)

SELECT Id,Name,LastModifiedDate
FROM Account
WHERE name like '%太郎%' Order by Name

複数条件の検索(and)

SELECT Id,Name,NumberOfEmployees
FROM Account
WHERE name like '%太郎%' and NumberOfEmployees > 10 Order by Name

同じ項目での複数条件検索(in)

SELECT Id,Name,NumberOfEmployees
FROM Account
WHERE name in ('田中太郎','鈴木花子')

日付検索(<,>)

SELECT Id,Name,CreatedDate 
FROM Account
WHERE CreatedDate >= 2005-10-08T00:00:00Z AND CreatedDate <= 2018-02-08T00:00:00Z
  • TODAY,THIS_WEEK,NEXT_MONTHの予約語もある

https://developer.salesforce.com/docs/atlas.ja-jp.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

件数カウント(COUNT)

SELECT COUNT(Id)
FROM Account

重複除外の件数カウント(COUNT_DISTINCT)

SELECT COUNT_DISTINCT(Name)
FROM Account

最小・最大(MIN、MAX)

SELECT MIN(NumberOfEmployees)
FROM Account
---
SELECT MAX(NumberOfEmployees)
FROM Account

SalesforceのId検索

SELECT Id,Name,LastModifiedDate
FROM Account
WHERE Id = '0017F00000Uxxxx' 
  • 結果が17桁のIdで帰ってくる

SQLではできるけど、SOQLでできないこと

アスタリスク

SELECT *
FROM Account
ORDER BY Name

Order Byの列数指定

SELECT Id,Name,LastModifiedDate
FROM Account
ORDER BY 1 desc

対応方法:項目を指定してあげる

テーブルに別名使えるが、項目には使えない

SELECT a.Id , a.Name as nm
FROM Account As a
Order by LastModifiedDate desc

Between検索

SELECT Id,Name,CreatedDate 
FROM Account
WHERE CreatedDate between 2005-10-08T00:00:00Z AND  2018-02-08T00:00:00Z

対応方法:<,>で代用する

空白検索(IS NULL)

SELECT Id,Name,LastModifiedDate
FROM Account
WHERE NumberOfEmployees IS NULL 

対応方法:以下のように指定する

SELECT Id,Name,NumberOfEmployees
FROM Account
WHERE NumberOfEmployees = null

UpdateとDelete文

対応方法:Apexを使う

haruto167
2018年から技術メモ残したいと思いQiitaはじめました!
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした