Anatomy of a SOQL query

10-minute read
SOQL query

The SOQL ( Salesforce Object Query Language ) query is the tool that allows reading from the database in Salesforce, whether in Apex code or for exporting data .

SOQL query

A SOQL query is made up of blocks (similar to clauses in a sentence) that always begin with a keyword. This article presents these keywords and the functionalities they enable. By convention, query-specific words are written in uppercase, but this is not mandatory for their operation.

We will look at different examples of queries, explaining the new keywords each time and showing how they work.

 

Level 1 – Structure of a basic query

Let's look at a simple first query to familiarize ourselves with the basics of a SOQL query:

SELECT Id, Name FROM Account WHERE Name = 'Toto' LIMIT 20

Here, we can already distinguish 4 blocks:

  • SELECT : allows you to specify the list of fields you want to retrieve, separated by commas (in this case, the identifier is the name).
  • FROM : identifies the type of object we want to retrieve (in this case, accounts).
  • WHERE : adds a filter to the query. Only records that meet this condition will be returned (here, accounts whose name is equal to 'Toto').
  • LIMIT : allows you to limit the number of records that will be returned (here, no more than 20).

The two truly essential keywords are SELECT and FROM, the others being optional.

 

Level 2 – Filter logic and ordering

The previous query presented a simple filter, but it is possible to use multiple fields to refine the search:

SELECT Id, Account.Name FROM Case WHERE CreatedDate = LAST_QUARTER AND (NOT (ContactEmail != null OR ContactMobile != null)) ORDER BY LastModifiedDate DESC LIMIT 10 OFFSET 10
  • LAST_QUARTER : This is a literal expression that represents a given period relative to today's date. In this example, only queries created during the previous quarter will be retrieved.
  • AND / OR / NOT : allows combining several criteria in the WHERE, based on the specified logic (parentheses are used to specify the order of priority).
  • ORDER BY : Allows you to sort the results according to one or more defined fields. The order can be ascending (ASC, default) or descending (DESC). Here, the most recently modified queries will appear first.
  • OFFSET : allows you to ignore a certain number of returned results. This can be useful in combination with LIMIT in a pagination system, for example, to retrieve only the results corresponding to a particular page.

 

Level 3 – Advanced Filters and Recycle Bin

Finally, regarding filters, there are more advanced functions that can meet specific needs:

SELECT Name FROM Opportunity WHERE StageName IN ('Prospecting', 'Qualification') AND Name LIKE '_Force%' AND Multipicklist__c INCLUDES ('Value1') ALL ROWS
  • IN / NOT IN : allows you to test a field from a list of values, and not just one as with the '=' operator.
  • LIKE : This is a comparison of text with a format . The symbol '_' matches any character, and the symbol '%' matches any string of characters (including empty strings).
  • INCLUDES / EXCLUDES : is a comparison operator used for multi-picklist fields (multiple choice drop-down lists), and allows you to check whether a value is present or not.
  • ALL ROWS : allows you to extend the query to records placed in the trash and archived activities, which are not otherwise taken into account.

 

Level 4 – Nested Queries, Scopes

When you query an object, it is also possible to retrieve its related records:

SELECT FIELDS(STANDARD), (SELECT Id, Name FROM Opportunities WHERE Amount >= 10) FROM Account USING SCOPE Mine
  • FIELDS : is a function that allows you to retrieve all the fields: STANDARD, CUSTOM , or all fields (FIELDS(ALL)).
  • (SELECT * FROM *) : This is a nested query that retrieves a list of records from a child object. Here, for example, each account will have a column containing a list of its related opportunities.
  • USING SCOPE : This allows you to define the visibility of the retrieved records. This example will only return accounts owned by the user. You can also request only records belonging to your team, for example.

SOQL 2 query

Level 5 – Groups

In some cases, it can be useful to retrieve a group of records. This allows you to perform various calculations on the rows within that group:

SELECT LeadSource, COUNT(Id), AVG(Amount), MAX(CloseDate) FROM Opportunity WITH SECURITY_ENFORCED GROUP BY LeadSource
  • GROUP BY : groups results according to a given field. A grouped query will return a list of AggregateResult containing the created groups. Each group contains the elements present in the SELECT statement and accessible in Apex via the get.
  • COUNT / AVG / MAX : These are aggregation functions used with GROUP BY that allow you to perform calculations on the elements of a group. For example, AVG will take the average of the specified field, MAX / MIN will take the maximum and minimum values, respectively. COUNT will count the records that have a value in the specified field. In our example, each row will contain the number of records, the average of the Amount , and the maximum value of the CloseDate.
  • WITH SECURITY_ENFORCED : This allows you to apply permissions to fields and objects in the query. If the user executing the query does not have the necessary rights to a field, they will not be able to retrieve it.

There are also functions for grouping by date, for example by month, fiscal year, or quarter (similar to the literal expressions discussed earlier). Note that grouping records loses information about individual records: you can only select the fields by which you are grouping and aggregates.

 

Level 6 – Advanced Group Functions

The previous query demonstrated the ability to group records by a single field. It is also possible to group them by multiple fields and to filter the groups:

SELECT AccountId, GROUPING(AccountId) grpAcc, Type, GROUPING(Type) grpType, SUM(Amount), MIN(CloseDate) FROM Opportunity GROUP BY ROLLUP(AccountId, Type) HAVING COUNT(ID) > 3

Here we can see the use of aliases after the aggregation functions ('grpAcc' in 'GROUPING(AccountId) grpAcc'), which make it easier to refer to them.

  • ROLLUP : This function combines with a GROUP BY clause to calculate and combine subtotals from the selected aggregates. In this example, the query will return one row for each AccountId/Type pair, one subtotal row for each AccountId value, and one row summarizing all records (all accounts).
  • GROUPING(field) : equals 1 if the row is a subtotal of the specified type and 0 otherwise. In this example, rows that group all types (corresponding to one account) will have grpType = 1, while rows corresponding to an AccountId/Type pair will have grpType = 0.
  • HAVING : defines a condition that groups (not individual records) must meet to be included in the query. Here, only groups containing more than 3 records will appear. This is the equivalent of WHERE, but applied to groups.

Also note the existence of the GROUP BY CUBE keyword , a variant of ROLLUP , which will calculate subtotals for all combinations of fields. To return to the example above, using GROUP BY CUBE will return the same rows as ROLLUP , but will also add subtotals for each Type value.

 

Level 7 – Polymorphic Relations

A polymorphic relationship is a field that can link several different objects, unlike look-ups (search relationships) or master-detail relationships, which can only represent a single object. For these relationships, the fields to retrieve will depend on the object contained within the relationship, so a specific syntax is used:

SELECT TYPEOF What WHEN Account THEN Phone, NumberOfEmployees WHEN Opportunity THEN Amount, CloseDate ELSE Name, Email END FROM Task FOR UPDATE

Here, we retrieve different fields depending on whether the relationship points to an account, an opportunity, or another object.

  • TYPEOF / END : specifies the polymorphic relation that we want to use in the filter, END terminates the block.
  •  WHEN / THEN / ELSE : allows you to specify the fields to query for each object ( ELSE representing all situations that would not have been dealt with previously).
  • FOR : specifies a particular treatment to apply to retrieved records, such as locking them in order to update them without having to worry about potential conflicts with FOR UPDATE. There are also FOR VIEW and FOR REFERENCE, which allow records in the "Recently Viewed" list to be included.

 

Level 8 – Special cases of SOQL queries

Some objects offer specific functionalities for SOQL queries, such as Salesforce's "Knowledge" articles:

SELECT Title FROM KnowledgeArticleVersion WITH DATA CATEGORY Group1__c ABOVE SubCategory1__c
  • WITH DATA CATEGORY : This filter allows you to filter articles based on their category within the Knowledge category hierarchy. It will therefore only retrieve articles from categories that meet the specified criteria (here, those whose category is above SubCategory1__c). Several selectors to determine where to search for the article within the given category (AT, BELOW, ABOVE_OR_BELOW).

The keyword WITH can also be used in other specific cases described in the documentation.

 

In summary

We've seen examples of SOQL queries that highlight the various features available to us. However, the list is not exhaustive, and some possibilities have not been presented in this article.

Note that there are several limitations that may apply to these queries, such as a limit on the number of rows that can be returned at once, as well as limits associated with certain objects. It is also important that the keywords appear in a specific order for the query to work.

The documentation goes into more detail about each feature to explain its uses and limitations.

If you would like to learn more about SOQL, check out Fabien Taillon's article !

Also read on the blog