Database Queries

You’ve seen how data structures in Apex are implicitly defined by the objects in your database. Force.com provides two query languages to populate these objects with data: Salesforce Object Query Language (SOQL) and Salesforce Object Search Language (SOSL). SOSL, addressed in Chapter 5, “Advanced Business Logic,” provides unstructured, full-text search across many objects from a single query.

The focus of this section is SOQL because it is the workhorse of typical business applications. This section includes subsections on the basics of SOQL, filtering and sorting, how to query related objects, and how to use SOQL from Apex code.

As you read this section, you can experiment with the sample SOQL queries using the Force.com IDE’s Schema Explorer. In the Navigator or Package Explorer View, expand the node for your Force.com Project and double-click salesforce.schema. Enter a query in the text box in the upper-left corner and click the Run Me button. The results appear in the table below the query. In Figure 4.5, a query has been executed against the Project object, returning four records. Note that many of the queries rely on objects from the Services Manager sample application rather than standard Force.com objects.

Figure 4.5 Running SOQL queries in Schema Explorer

A SOQL statement is centered on a single database object, specifying one or more fields to retrieve from it. The fields to select are separated by commas. Listing 4.26 is a simple SOQL statement that returns a list of Account records with Id and Name fields populated. SOQL is not case sensitive. SOQL keywords are shown throughout the book in uppercase and metadata objects in title case for readability only.

Listing 4.26 Simple SOQL Statement

SELECT Id, Name
  FROM Account

Valid operators are > (greater than), < (less than), >= (greater than or equal to), <= (less than or equal to), = (equal to), != (not equal to), IN and NOT IN (matches a list of literal values, and supports semi-joins and anti-joins), and INCLUDES and EXCLUDES (match against multi-select picklist values). On String fields, the LIKE operator is also available, which applies a pattern to filter records. The pattern uses the % wildcard to match zero or more characters, _ to match one character, and the character to escape the % and _ wildcards, treating them as regular characters.

Multiple filters are combined in a single SOQL statement using the Boolean operators AND and OR and grouped with parentheses. Listing 4.27 returns the names of accounts with a type of direct customer, a modification date sometime during the current year, and more than $100 million in annual revenue.

Listing 4.27 SOQL Statement with Filter Conditions

Click here to view code image

SELECT Name
  FROM Account
  WHERE AnnualRevenue > 100000000
  AND Type = 'Customer - Direct'
  AND LastModifiedDate = THIS_YEAR

Notice the way literal values are specified. Single quotation marks must be used around String literals but never with other data types. THIS_YEAR is a built-in relative time function. The values of relative time functions vary based on when the query is executed. Other relative time functions are YESTERDAY, TODAY, TOMORROW, LAST_WEEK, THIS_WEEK, NEXT_WEEK, and so forth.

Absolute dates and times can also be specified without single quotation marks. Dates must use the YYYY-MM-DD format. Datetimes can be YYYY-MM-DDThh:mm:ssZ, YYYY-MM-DDThh:mm:ss+hh:mm, or YYYY-MM-DDThh:mm:ss-hh:mm, indicating the positive or negative offset from Coordinated Universal Time (UTC).

In addition to filter conditions, SOQL supports the LIMIT keyword. It sets an absolute upper bound on the number of records that can be returned from the query. It can be used in conjunction with all the other SOQL features. For example, the SOQL statement in Listing 4.28 returns up to ten Account records modified today.

Listing 4.28 SOQL Statement with Record Limit

Click here to view code image

SELECT Name, Type
  FROM Account
  WHERE LastModifiedDate = TODAY
  LIMIT 10

Listing 4.29 SOQL Statement with Sort Fields

Click here to view code image

SELECT Name, Type, AnnualRevenue
  FROM Account
  ORDER BY Type, LastModifiedDate DESC

The two ways to navigate object relationships in SOQL are child-to-parent and parent-to-child. Listing 4.30 is an example of a child-to-parent query, returning the name, city, and Force.com username creating its contact of all resources with a mailing address in the state of California. It selects and filters fields of the Project object, the parent object of Account. It also selects the Name field from the User object, a parent two levels removed from Project via the Account’s CreatedBy field.

Listing 4.30 SOQL with Child-to-Parent Relationship

Click here to view code image

SELECT Name, Account__r.Name, Account__r.CreatedBy.Name
  FROM Project__c
  WHERE Account__r.BillingState = 'CA'

At most, five levels of parent objects can be referenced in a single child-to-parent query, and the query cannot reference more than 25 relationships in total.

The second form of relationship query is the parent-to-child query. Listing 4.31 provides an example. The parent object is Resource, and the child is Timecard. The query selects from every Contact its Id, Name, and a list of hours from its Timecards in the current month.

Listing 4.31 SOQL with Parent-to-Child Relationship

Click here to view code image

SELECT Id, Name,
  (SELECT Total_Hours__c
    FROM Timecards__r
    WHERE Week_Ending__c = THIS_MONTH)
  FROM Contact

A parent-to-child query cannot reference more than 20 child objects. Double-clicking the parent record in the results table brings up the child records for viewing in the Force.com IDE.

Listing 4.32 is an example of a SOQL query used in Apex. It retrieves a list of Project records for this year and loops over them, summing their billable hours in the variable totalHours. Note the usage of the variable named statuses directly in the SOQL query, preceded by a colon. This is known as a bind variable. Bind variables can appear on the right side of a WHERE clause, as the value of an IN or NOT IN clause, and in the LIMIT clause.

Listing 4.32 SOQL Query in Apex

Click here to view code image

Decimal totalHours = 0;
List<String> statuses = new String[] { 'Green', 'Yellow' };
List<Project__c> projects = [ SELECT Billable_Hours__c
  FROM Project__c
  WHERE Start_Date__c = THIS_YEAR and Status__c IN :statuses ];
for (Project__c project : projects) {
  totalHours += project.Billable_Hours__c;
}
System.debug(totalHours);

This code relies on a List to store the results of the SOQL query. This means the entire SOQL query result must fit within the heap size available to the program. A better syntax for looping over SOQL records is a variation of the List/Set Iteration For Loop called a SOQL For Loop. The code in Listing 4.33 is a rewrite of Listing 4.32 using the SOQL For Loop. This allows it to run when the Project object contains up to 50,000 records for this year without consuming 50,000 records’ worth of heap space at one time.

Listing 4.33 SOQL Query in Apex Using SOQL For Loop

Click here to view code image

Decimal totalHours = 0;
for (Project__c project : [ SELECT Billable_Hours__c
  FROM Project__c
  WHERE Start_Date__c = THIS_YEAR ]) {
  totalHours += project.Billable_Hours__c;
}
System.debug(totalHours);

An additional form of the SOQL For Loop is designed for use with Data Manipulation Language (DML). Consider how the code in Listing 4.32 could be adapted to modify Project records returned from the SOQL query rather than simply summing them. With the existing code, one Project record would be modified for each loop iteration, an inefficient approach and a quick way to run afoul of the governor limits. But if you change the type of variable in the For Loop to a list of Project records, Force.com provides up to 200 records per loop iteration. This allows you to modify a whole list of records in a single operation.

Any valid SOQL statement can be executed in Apex code, including relationship queries. The result of a child-to-parent query is returned in a List of objects whose types match the child object. Where fields from a parent object are included in the query, they are available as nested variables in Apex code. For example, running the query in Listing 4.30 within a block of Apex code returns a List<Project__c>. If this List is assigned to a variable named projects, the first Account record’s billing state is accessible by projects[0].Account__r.BillingState.

Parent-to-child queries are returned in a List of objects, their type matching the parent object. Each record of the parent object includes a nested List of child objects. Using Listing 4.31 as an example, if results contains the List<Contact> returned by the query, results[0].Timecards__r[0].Total_Hours__c accesses a field in the first Contact’s first Timecard child record.