SOQL Cheat Sheet
I still remember that nightmare day when I just pressed the ‘Enter’ button and the moment the whole world collapsed in front of me saying “You are the culprit” !!!!!!!
Yes, it was when I started working as a software developer in PHP and MySQL for a Government project in Trivandrum. I had completed the development of scholarship project for school students and the database was updated with all student details. We were expecting a demo on the next day with all district coordinators and I had to do some basic sanity as advised by my mentor. As an amateur beginner, I was performing sanity in production database itself. I found some of the records from one education district are redundant and those records needed to be deleted. With full confidence, I had entered the delete query on MySQL console and pressed “Enter”. Complete database data got vanished in a fraction of seconds. Now, hope you can relate that sad bgm.
I could have analysed the ramifications before I did that mistake. But, having said that the incident at the beginning of my career taught me a lot, especially when playing with queries.
In our day to day development life, we might have needed queries to execute to fetch a set of records based on certain conditions. And, many a time, I found the requirement in different projects are similar though the ‘from’ object may differ. So I always keep a SOQL cheat sheet with me to use it according to the business needs.
- Get most recently created 10 records from an object
SELECT Id, Name FROM object ORDER BY CreatedDate Desc LIMIT 10
Eg: Select Id, Name FROM Opportunity ORDER BY CreatedDate Desc LIMIT 10
2. Get all those parent records that don’t have a related child with a subquery.
SELECT Id, Name FROM Parent__c WHERE Id NOT IN (SELECT ParentId__c FROM Child__c )
Eg: To get all those accounts which don't have any contacts.
SELECT Id, Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Contact)
3. Queries with Date conditions
There are 2 date field types — date and DateTime. Eg: CloseDate in Opportunity is a Date field whereas createdDate is a DateTime field. We need to query these accordingly.
Eg 1: SELECT Id, Name FROM Opportunity WHERE closedate = TOMORROW
Here, we can compare closedate with the macro(TODAY) directly, since both are in date format.
Eg 2: SELECT Id, Name FROM Opportunity WHERE DAY_ONLY(CreatedDate) = TODAY
DAY_ONLY is the function to extract date format from DateTime format. Apart from TODAY & TOMORROW, there are plenty of inbuilt macros which can be used as per your requirement. Here is the list,
YESTERDAY, THIS_WEEK, NEXT_MONTH, LAST_90_DAYS, LAST_MONTH, THIS_YEAR.
Eg 3: To get the records between a specified date range
SELECT Id,Name FROM Account WHERE day_only(createddate) < 2021–01–01 AND day_only(createddate) > 2020–12–22
4. Queries with aggregate functions and usage of “HAVING” clause
These are the aggregate functions which are available in SOQL.
COUNT(), COUNT(FieldName), AVG(), SUM(), MIN(), MAX()
Eg 1: SELECT CampaignId, AVG(Amount) FROM Opportunity GROUP BY CampaignId
Eg 2: SELECT COUNT() FROM Account WHERE Name LIKE ‘TEST%’
Eg 3: SELECT COUNT_DISTINCT(Industry) FROM Account
Eg 4: SELECT MIN(CreatedDate), FirstName, LastName FROM Contact GROUP BY FirstName, LastName
Eg 5: SELECT SUM(Amount) FROM Opportunity WHERE IsClosed = false AND Probability > 60
5. Sub Queries
Let's take the classic case of Opportunity and its child object, OpportunityProduct.
Eg 1: SELECT Id, Name, (SELECT Product2.Name From OpportunityLineItems) FROM Opportunity WHERE StageName NOT IN (‘Closed Won’, ‘Closed Lost’)
Here, both Opportunity and OpportunityProduct are standard objects. Also, please make a note that the plural form of the object is being used in the inner query.
Eg 2: SELECT Id, Name, (SELECT Name FROM Contacts)
Be attention that the object name has to be Child Relationship Name when subquery SELECT as a field.
Eg 3: SELECT Id, Name,(select Id, Name from ChildObjects__r) FROM Parent__c WHERE Id IN (SELECT ParentId__c FROM Child__c )
“ChildObjects__r” is the “Child Relationship Name” which can be found in “Custom Field Definition Detail” page
Note: We need to consider the count of records retrieved in all the above cases. To avoid governor limit issues, please be noted that we should use proper filters in where clause to narrow down the results.
You can add other frequent queries in the comment section. Let’s make it bigger :)
