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
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 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.
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:
- Learning to Fly with Force.com [Article]
- Working with Home Page Components and Custom Links [Article]
- Salesforce CRM Functions [Article]
|Revolutionize the use of simple query strings to make them more efficient using SOQL with this book and ebook|
eBook Price: £10.99
Book Price: £17.99
About the Author :
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.