Part 1 | Static SOQL
The ETL process in a Data Integration System is a well known pattern to Extract, Transform, and Load data from one environment to another. There is a similar process when you develop applications in the Salesforce Platform. I called it RMP, which stands for Retrieve, Modify, and Persist. If you think about it, 90% of your applications in the cloud follow these core steps:
- Retrieve data from your objects in the database,
- Present the data to your users, and let them alter it,
- Save the altered data back to the database.
Whether you are creating a Visualforce page or a lightning application, it will all begin with the Retrieve action. In the Salesforce ecosystem, there are two languages you can use to fetch what you need from the database: SOQL and SOSL.
SOQL stands for Salesforce Object Query Language, and if you are into dogs breeds, this would be your golden retriever. SOQL was specifically designed by Salesforce for developers who know what they want to retrieve, where they want to search for it and how they want to filter it.
The syntax for static SOQL is simple, and it’s also very similar to SQL. Here is an example of a basic statement:
SELECT fieldList FROM objectType
“SELECT” and “FROM” are keywords and are mandatory; “fieldList” and “objectType” are placeholder values that will be replaced by actual values. These are also required.
Let’s look at a classic example: we need to retrieve the name and phone number of all accounts from our database. Here is what the SOQL would look like:
The quickest way to run static SOQL is by using the Query Editor in the Developer Console; here is how to do it:
Let’s level up a bit. Here is another SOQL statement:
In the example above, we introduced a new keyword “WHERE”, and this is how we tell the platform that we are searching not just any account but accounts with a specific criterion. From this point on, things get a lot more interesting, so buckle up:
A SOQL query can:
- Filter by multiple criteria:
SELECT Name, Phone FROM Account WHERE Industry = ‘Energy’ AND Type = ‘Customer – Direct’
SELECT Name, Phone FROM Account WHERE Industry = ‘Energy’ OR Type = ‘Customer – Direct’
- Access fields from parent records:
SELECT Name, Phone, Account.Name FROM Contact WHERE Account.Industry = ‘Energy’
- Access child records related to a Parent
SELECT Name, Phone, (Select Name FROM Contacts) FROM Account WHERE Name = ‘Edge Communications’
There is so much more a SOQL statement can do, but before we go deeper, a key concept to understand is that a SOQL query can return:
- A single record
- A list of records
- An empty list
- An integer
- An Aggregated Result
The first two are kind of obvious, but just to be sure, let’s run a couple Apex statements in the developer console:
Retrieving a single sObject
Account retrievedAccount = [SELECT Name, Phone FROM Account WHERE Name = ‘Edge Communications’];
system.debug(‘the account ‘+retrievedAccount);
In my environment Org, there is only one account named “Edge Communications”, so the above statement only returns one account. That’s why we can directly assign the result to a variable named “retrievedAccount”.
Retrieving a List of sObjects or an Empty List
List<Account> retrivedAccounts = [SELECT Name, Phone FROM Account WHERE Industry = ‘Energy’];
system.debug(‘the accounts ‘+retrievedAccounts);
In this scenario, the SOQL statement returns a List of Accounts that meet the criteria defined in the WHERE clause. However, the following code snippet will result in an empty list being returned:
List<Account> retrivedAccounts = [SELECT Name, Phone FROM Account WHERE Industry = ‘Mining’];
system.debug(‘the accounts ‘+retrievedAccounts);
The statement above returns an empty list because in my Salesforce Org, there are no accounts with “Mining” as the value in the industry field.
Here we can establish one of the golden rules of SOQL:
Unless you can guarantee that your SOQL statement will return at least 1 and no more than 1 record, always use a List<sObject> as your variable to hold the result of the query.
If you break this golden rule, you will get the following error:
Or, if your query returned no elements:
Using the Count() function
Integer quantityOfAccounts = [SELECT COUNT() FROM Account];
system.debug(‘the total Accounts ‘+quantityOfAccounts);
This function is very lightweight, and a great help when you only need to know the number of records in your database that meet certain criteria.
Using Functions to Aggregate
Although Aggregate Results is an advanced topic that we will cover in depth a later post, I still want to give you a sneak peek of how smart SOQL is.
Here is a scenario:
You need to run a script that outputs the sum of all “Closed Won” opportunities in your Salesforce Org.
Option One | Use Apex to perform the aggregation:
Option Two | Use SOQL Functions to perform the aggregation:
At a glance, you can see that by using Aggregate Results, you can save a couple of lines in the script. But, that’s not what is important; the big difference between the two approaches is performance:
Using Apex to perform the aggregation:
Letting SOQL perform the aggregation:
As you can see, using Aggregate Results is not only 2 milliseconds faster, but It also has a smaller impact on the Heap Size, which constitutes one of the Governor Limits we need to respect.
In this post, we got our feet wet learning about Static SOQL. Stay tuned for Part 2 where we cover advanced features and techniques.
About the Author
Clara Perez is a Salesforce MVP and Lead Developer at Great Wave who loves teaching Salesforce concepts.