Getting Started with SOQL (2014)

Chapter 3. Advanced SOQL Statements

In the previous chapter, we saw the basic SOQL statements that deal with one object in a SOQL statement. This chapter gives more information on how to write advanced SOQL statements. This chapter 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.

Filtering a multiselect picklist field using the INCLUDES and EXCLUDES operators will be discussed in detail. In Chapter 2, Basic SOQL Statements, only the definition was given for the INCLUDES and EXCLUDES operators.

The grouping of records with more than one field using GROUP BY ROLLUP and GROUP BY CUBE will be explained here with examples. Sorting the records in both the ascending and descending orders together in a SOQL statement will also be further discussed.

Relationship queries

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

Let us see an example for relationship queries with standard objects to query the records. The relationship between Account and Contact is a lookup. Account is the parent object, and Contact is the child object. Account can have multiple Contacts.

A sample query is given as follows:

SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account

The preceding query will retrieve all the accounts and their associated Contacts. The following screenshot shows the output of the SOQL execution:

Let us see another example for relationship queries with custom objects to query the records. The relationship between Employee and Hobby is a master-detail relationship. Employee is the parent object, and Hobby is the child object. Employee can have multiple Hobbies.

A sample query is given as follows:

SELECT Employee_Name__c, (SELECT Id, Name FROM Hobbies__r) FROM Employee__c

The preceding query will retrieve all the names of the employees and their associated Hobbies. The following screenshot shows the output of the SOQL execution:

To find out the name of the relationship, go to the objects' definition and select the relationship field. The Child Relationship Name option gives us the relationship name. The __r symbol should be added along with the child relationship name to query in the case of custom relationships. For standard relationships, we should not add __r. The following steps will help us to get the relationship name for relationship queries:

1. Navigate to Setup | Build | Create | Objects as shown in the following screenshot:

2. Select the child object. In our example, Hobby is our child object as shown in the following screenshot:

3. Select the relationship field:

4. The Child Relationship Name option denotes the relationship name. We have to add __r to the child relationship name when querying the records using SOQL. In the case of a standard relationship, we need not add __r when we query the records using SOQL.

Note

A custom relationship in Salesforce.com always ends with __r. But a standard relationship, which exists between the standard Salesforce.com objects, does not end with __r.

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.

Let us see a few examples of filtering the multiselect picklist values. Skills__c is a multiselect picklist field.

The INCLUDES operator

The INCLUDES operator is used to retrieve the records that contain any one of the specified values.

A sample query is given as follows:

SELECT Employee_Name__c, Skills__c FROM Employee__c WHERE Skills__c INCLUDES ('C', 'C#')

The preceding query will fetch the names of all the employees whose Skills match either with C or C#. The following screenshot is the output of the SOQL execution:

Let us see an example for including a null value.

A sample query is given as follows:

SELECT Employee_Name__c, Skills__c FROM Employee__c WHERE Skills__c EXCLUDES ('C', 'C#', '')

The preceding query will fetch the names of all employees whose Skills match either with C or C# and null. The following screenshot shows the output of the SOQL execution:

The EXCLUDES operator

The EXCLUDES operator is used to retrieve the records that do not contain any one of the specified values.

A sample query is given as follows:

SELECT Employee_Name__c, Skills__c FROM Employee__c WHERE Skills__c EXCLUDES ('C', 'C#')

The preceding query will fetch the names of all employees whose Skills do not match either with C or C#. The following screenshot shows the output of the SOQL execution:

Let us see an example for excluding the null value.

A sample query is given as follows:

SELECT Employee_Name__c, Skills__c FROM Employee__c WHERE Skills__c EXCLUDES ('C', 'C#', '')

The preceding query will fetch the names of all the employees whose Skills do not match either with C or C# and null. The following screenshot shows the output of the SOQL execution:

The escape sequences

An escape character is a character that invokes an alternative interpretation of the subsequent characters in a character sequence. The following table shows the list of escape sequences that can be used in the SOQL statements:

The date formats

When querying the records using the date field or the date and time field in the SOQL statement, the date formats should be followed. The following table shows the list of date formats that can be used in the SOQL statements:

The date literals

When querying the records using the date field in the SOQL statement, the date literals can be used. The following table shows the list of date literals that can be used in the SOQL statements:

Querying with the date fields

Let us see an example for querying with the date field using one of the date literals we just covered.

A sample query is given as follows:

SELECT Employee_Name__c, Joining_Date__c FROM Employee__c WHERE Joining_Date__c < LAST_MONTH

The preceding query will fetch the names of all the employees whose joining date values are less than the values for the current date last month. The following screenshot shows the output of the SOQL execution:

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.

A sample query is given as follows:

SELECT City__c, State__c, COUNT(Employee_Name__c) Counts FROM Employee__c GROUP BY ROLLUP(City__c, State__c)

The following screenshot shows the output of the SOQL execution:

In the previous example, we saw both the statewise and citywise count. This GROUP BY ROLLUP clause will be very useful to us when we get a chance to work with the Visualforce charting feature.

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.

A sample query is given as follows:

SELECT City__c, State__c, LastReferencedDate FROM Employee__c FOR REFERENCE

When we execute the preceding query for the first time, it shows the last reference date of the record in the LastReferencedDate column. However, for the second time, all the records will show the same date and time (the date and time when we executed the query for the first time) for LastReferencedDate.

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.

A sample query is given as follows:

SELECT City__c, State__c, LastViewedDate FROM Employee__c FOR VIEW

When we execute the preceding query for the first time, it shows the last viewed date of the record in the LastViewedDate column. However, for the second time, all the records will show the same date and time (the date and time when we executed the query for the first time) for LastViewedDate.

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.

A sample query is given as follows:

SELECT City__c, State__c, GROUPING(City__c) CityGroup, GROUPING(State__c) StateGroup, COUNT(Id) IdCount FROM Employee__c GROUP BY CUBE(City__c, State__c)

The following screenshot shows the output of the SOQL execution:

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 LIMIT is very useful in retrieving a subset of the records. The OFFSET usage in SOQL has many limitations and restrictions. The limitation and guidelines for using OFFSET will be discussed in detail in Chapter 5, Limitations and Best Practices.

A sample query is given as follows:

SELECT Name FROM Account OFFSET 100

The preceding query will fetch all the accounts starting from the row number 101. The first 100 records will not be fetched.

Let us see an example for OFFSET along with LIMIT.

A sample query is given as follows:

SELECT Name FROM Account OFFSET 100 LIMIT 50

The preceding query will fetch all the accounts starting from row number 101 to 150. Only these 50 records will be fetched. The OFFSET clause along with LIMIT helps to create the pagination concept in the Visualforce page very easily. The offset calculation is done on the server side. So, we have to be very careful when implementing pagination since it fetches fresh data for each query call.

Salesforce.com recommends that we use the ORDER BY clause whenever we use OFFSET. Using the ORDER BY clause along with OFFSET ensures that the ordering of the result set is consistent.

Summary

In this chapter, 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.

Filtering multiselect picklist field values using the INCLUDES and EXCLUDES operators was explained. The grouping of records using GROUP BY ROLLUP and GROUP BY CUBE were also discussed.

To find the last viewed date of the record and to find the last referenced date of the record using FOR VIEW and FOR REFERENCE, respectively, were also explained. We also discussed pagination in the Visualforce page using OFFSET and LIMIT.

The next chapter deals with the functions that are available in SOQL. The functions help us to reuse the commands instead of writing conditions again and again.