In the previous post, we covered the basics of SOQL, the language Salesforce created to retrieve records from your database. Today, we are going to cover advanced features that will redefine the way you code in the Platform.
Let’s say you created a custom multi-select picklist in your Contact object called “Affiliation__c” with values “Member”, “Partner”, and “Founder”. You want to query all Contacts who have values Members AND Partners, OR Founder. First, let’s construct the query:
SELECT FirstName FROM Contact WHERE Affiliation_c includes (‘Member’;’Partner’,’Founder)
Using a semicolon to separate values of a picklist in a SOQL is the equivalent to AND’ing the values, while using a comma is the same as OR’ing the values. This is how the query would look like without using this neat trick:
SELECT FirstName FROM Contact WHERE(Affiliation_c=’Member’ AND Affiliation_c=’Partner’) OR Affiliation_c=’Founder
Polymorphic Relationship Field
In Salesforce, there are some unique fields that can relate to different objects. For example, the “Related To” (WhatId) field in the Task object can be related to: an Account, an Opportunity, a Case, a Campaign or any Custom object. These types of fields are called Polymorphic Relationships. You can take advantage of these fields in SOQL by filtering based on their type.
Here is a scenario: You need to run an script to update all Tasks related to an Account or an Opportunity to “Completed” if they were created last year.
By using the Type qualifier on the What polymorphic relationship field, we just saved a ton of work! This is what the script would have looked like if we didn’t use the Type qualifier:
Notice the extra “IF” statement on line 9 that manually filters the tasks associated with an Account or an Opportunity. In addition, we need to maintain separate lists of Tasks for the records we modified, lines 5 and 12.
Bonus Tip: Use Date Literals to calculate and filter by dates in your SOQL statements.
The OFFSET clause is a very handy feature in SOQL, especially when your query statement returns a very large set of data. It allows you to “jump” a specific number of records, and display the subsequent records. If this makes you think of pagination, you are right! OFFSET is commonly used to paginate your search results.
SELECT Name FROM Account ORDER BY Name LIMIT 20 OFFSET 10
The statement above returns the last 10 Accounts retrieved by the SOQL query. Notice the ORDER BY clause, which is required to get the results back in the same order.
How would you build a pagination feature based on the OFFSET keyword? Here is an example.
GROUP BY, Aggregate function and HAVING
The more you learn about SOQL, the easier your life as a developer gets. GROUP BY is a SOQL clause that allows you to group your results by one or more criteria. Aggregate Functions offer additional insights into your data within SOQL. HAVING allows you to filter even further, using the result of an aggregate function.
The power of these three clauses is evident when used together. Here is an example:
In my developer Org, I have 31 Opportunity records:
I can get the total worth of all my Opportunities using the SUM() Aggregate Function:
Additionally, I can combine the SUM() Aggregate Function with the GROUP BY clause to get the total Opportunity value for each Account.
Or, I can focus on just those Accounts whose total worth is over 300k dollars:
All of that is achieved with just one SOQL query statement. If you’ve been a Salesforce developer for a while, you know that creating Apex code to perform all the above calculations is cumbersome and prone to error, besides having severe performance impacts on the servers.
Here is a list of other SOQL functions that are worth learning more about:
- toLabel() – Utilized on fields to translate query results into the user’s language.
- FORMAT() – Applies localized formatting to numbers, dates, time and currency fields.
- FOR VIEW – Adds the query results to the “Recent Item View” list in the Salesforce sidebar.
- FOR REFERENCE – Adds the related records (children) of the query results to the “Recent Item View”.
- FOR UPDATE – Locks the records queried, so no other user can modify them.
- GROUP BY ROLLUP – Enables SOQL to calculate subtotals of aggregated data.
- GROUP BY CUBE – Enables SOQL to calculate subtotals for all combinations of the GROUP BY fields.
I hope you enjoyed learning about the ever-growing features SOQL provides to make your coding life easier. In the next post we will cover the Relationship Query, which is a must for a Developer’s toolkit.
About the Author
Clara Perez is a Salesforce MVP and Lead Developer at Great Wave who loves teaching Salesforce concepts.