Advanced SOQL Statements

Exclusive offer: get 50% off this eBook here
Getting Started with SOQL

Getting Started with SOQL — Save 50%

Revolutionize the use of simple query strings to make them more efficient using SOQL with this book and ebook

$16.99    $8.50
by Magulan D | April 2014 | e-Commerce Open Source

This article by Magulan D., the author of the book Getting Started with SOQL, gives more information on how to write advanced SOQL statements. This article deals with querying the records for one or more objects in a single SOQL statement. In this case, there should be some relationship among the objects. In Salesforce.com, we cannot query the records from two or more objects if they don't have a relationship between them. The relationships that are available in Salesforce.com are lookup relationship and master-detail relationship.

(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:


Getting Started with SOQL Revolutionize the use of simple query strings to make them more efficient using SOQL with this book and ebook
Published: April 2014
eBook Price: $16.99
Book Price: $27.99
See more
Select your format and quantity:

About the Author :


Magulan D

Magulan D is a Salesforce.com administrator and developer. He started his career as a PHP developer and also worked as a Siebel CRM developer. During his career as a PHP developer, he created many sites.

Magulan is also a blogger, posting many useful tutorials relating to Salesforce.com development and administration works. Often these are workarounds for problems or issues that people usually face in their development. He has been working as a Salesforce.com developer since 2011.

Books From Packt


Developing Applications with Salesforce Chatter
Developing Applications with Salesforce Chatter

Salesforce CRM: The Definitive Admin Handbook - Second Edition
Salesforce CRM: The Definitive Admin Handbook - Second Edition

Getting Started with NoSQL
Getting Started with NoSQL

Force.com Development Blueprints
Force.com Development Blueprints

Force.com Tips and Tricks
Force.com Tips and Tricks

 Microsoft SQL Server 2012 with Hadoop
Microsoft SQL Server 2012 with Hadoop

 Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube
Instant Microsoft SQL Server Analysis Services 2012 Dimensions and Cube

Microsoft SQL Server 2012 Security Cookbook
Microsoft SQL Server 2012 Security Cookbook


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software