SOQL Cheat Sheet

Introduction

My Salesforce SOQL Cheat Sheet is a guide I created for myself and that I’d like to share with others. All the things I’m constantly forgetting in SOQL in one neat, tidy place!

This is a living document; I add to it as I find new things I’d like to remember, and it isn’t strictly SOQL – as you’ll see I show some examples of how to properly embed SOQL within Apex.

SOQL Snippets

…the useful stuff!

General Query Formatting
SELECT FieldName FROM ObjectName [WHERE FieldName = 'FieldValue' ORDER BY FieldName DESC LIMIT 5]
Dynamic SOQL – as a String using Database.query()
String queryString = 'SELECT Id, FirstName, LastName FROM Lead WHERE CreatedDate = LAST_N_DAYS:7'; List<Lead> myLeads = Database.query(queryString);

Remember, you can dynamically manipulate the string to change the query before it executes.

Assignment to List in Apex
List<ObjectName> queryResults = [SELECT FieldName FROM ObjectName WHERE FieldName = 'FieldValue' ORDER BY FieldName DESC LIMIT 5];
Directly in Apex for loop
for (Custom_Object__c co: [SELECT Id, Name FROM Custom_Object__c WHERE CreatedDate = THIS_MONTH]) {
    runSomeFunction(co); 
}
LIKE with % (Wildcard)
SELECT Id FROM Lead WHERE Email LIKE '%@sfdc.guide'
IN with Static Values
SELECT Id, Name FROM Account WHERE BillingState IN ('WA','OR','CA')
IN with a Nested Query
SELECT Id, Name FROM Opportunity WHERE AccountId IN (SELECT Id FROM Account WHERE Name = 'Zoom')
COUNT()
SELECT COUNT(Id) FROM Lead WHERE CreatedDate = THIS_MONTH
COUNT_DISTINCT()
SELECT COUNT_DISTINCT(Account.Name) FROM Opportunity WHERE CloseDate = LAST_MONTH

Aggregate Results

AggregateResult[] ar = [SELECT AVG(Amount) average FROM Opportunity];

Decimal avgAmount = (Decimal)ar[0].get('average');

System.debug(avgAmount);
Date Literals

Full Documentation Here

SELECT Id, Name, CreatedDate, LeadSource FROM Lead WHERE CreatedDate = [DATE_LITERAL]

Replace [DATE_LITERAL] with one of the following values (this is a subset of the full list)…

TODAY, TOMORROW, YESTERDAY,
LAST_WEEK, THIS_WEEK, NEXT_WEEK
THIS_QUARTER, THIS_YEAR, LAST_YEAR
LAST_N_WEEKS:2, LAST_N_MONTHS:6, LAST_N_YEARS:2
HAVING

Since we can’t use WHERE with GROUP BY and aggregate functions, but we can use HAVING, like so…

SELECT Name, Count(Id) FROM Account GROUP BY Name HAVING Count(Id) > 1

The above is a query which returns accounts with duplicate names. I think I stole it from somewhere else. That’s why it’s called a cheat sheet.

Add comment

Recent Posts

Categories