Advanced SOQL Statements

Getting Started with SOQL


April 2014

$16.99

Revolutionize the use of simple query strings to make them more efficient using SOQL.

(For more resources related to this topic, see here.)

Relationship queries

Relationship queries are mainly used to query the records from one or more objects in a single SOQL statement in Salesforce.com. We cannot query the records from more than one object without having a relationship between the objects.

Filtering multiselect picklist values

The INCLUDES and EXCLUDES operators are used to filter the multiselect picklist field. The multiselect picklist field in Salesforce allows the user to select more than one value from the list of values provided.

Sorting in both the ascending and descending orders

Sometimes, we may get a chance to sort the records when we fetch these using the SOQL statements based on two fields, one field in the ascending order and another field in the descending order. The following sample query will help us to achieve this easily:

SELECT Name, Industry FROM Account ORDER By Name ASC, Industry DESC

Using the preceding SOQL query, the accounts will first be sorted by Name in the ascending order and then by Industry in the descending order. The following screenshot shows the output of the SOQL execution:

First, the records are arranged in the ascending order of the account's Name, and then it is sorted by Industry in the descending order.

Using the GROUP BY ROLLUP clause

The GROUP BY ROLLUP clause is used to add subtotals for aggregated data in query results. A query with a GROUP BY ROLLUP clause returns the same aggregated data as an equivalent query with a GROUP BY clause. It also returns multiple levels of subtotal rows. You can include up to three fields in a comma-separated list in a GROUP BY ROLLUP clause.

Using the FOR REFERENCE clause

The FOR REFERENCE clause is used to find the date/time when a record has been referenced. The LastReferencedDate field is updated for any retrieved records. The FOR REFERENCE clause is used to track the date/time when a record has been referenced last while executing a SOQL query.

Using the FOR VIEW clause

The FOR VIEW clause is used to find the date when a record has been last viewed. The LastViewedDate field is updated for any retrieved records. The FOR VIEW clause is used to track the date when the record was viewed last while executing a SOQL query.

Using the GROUP BY CUBE clause

The GROUP BY CUBE clause is used to add subtotals for every possible combination of the grouped field in the query results. The GROUP BY CUBE clause can be used with aggregate functions such as SUM() and COUNT(fieldName). A SOQL query with a GROUP BY CUBE clause retrieves the same aggregated records as an equivalent query with a GROUP BY clause. It also retrieves additional subtotal rows for each combination of fields specified in the comma-separated grouping list as well as the grand total.

Using the OFFSET clause

The OFFSET clause is used to specify the starting row number from which the records will be fetched. The OFFSET clause will be very useful when we implement pagination in the Visualforce page. The OFFSET clause along with Limits very useful in retrieving a subset of the records. The OFFSET usage in SOQL has many limitations and restrictions.

Summary

In this article, we saw how to query the records from more than one object using the relationship queries. The steps to get the relationship name among objects were also provided. Querying the records using both standard relationship and custom relationship was also discussed.

Resources for Article:


Further resources on this subject:


Books to Consider

comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free