Using SOQL in Apex

Like database objects,SOQL queries are an integrated part of the Apex language.They are

developed in-line with your code and verified at compile time against your database schema.

Listing 5-31 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 invoiced 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 5-31 SOQL Query in Apex

Decimal totalHours = 0;
List<String> statuses = new String[] { 'Green', 'Yellow' };
List<Proj__c> projects = [ SELECT Total_Billable_Hours_Invoiced__c FROM Proj__c WHERE Start_Date__c = THIS_YEAR and Status__c IN :statuses ];

for (Proj__c project : projects) {
totalHours += project.Total_Billable_Hours_Invoiced__c;
}

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 5-32 is a rewrite of Listing 5-31 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 5-32 SOQL Query in Apex Using SOQL For Loop
Decimal totalHours = 0;
for (Proj__c project : [ SELECT Total_Billable_Hours_Invoiced__c
FROM Proj__c
WHERE Start_Date__c = THIS_YEAR ]) {
totalHours += project.Total_Billable_Hours_Invoiced__c;
}

An additional form of the SOQL For Loop is designed for use with Data Manipulation
Language (DML). Consider how the code in Listing 5-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.

Note

Looping through a list of records to calculate the sum of a field is provided as an example of
using SOQL with Apex. It is not an optimal way to perform calculations on groups of records
in the database. 

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
5-29 within a block of Apex code returns a List<Resource__c>. If this List is assigned to
a variable named resources, the first Resource record’s mailing city is accessible by
resources[0].Contact__r.MailingCity.

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 5-30 as an example, if results contains the List<Resource__c> returned by the
query, results[0].Timecards__r[0].Total_Hours__c accesses a field in the first
Resource’s first Timecard child record.

Note

Usage of SOQL in Apex is subject to governor limits. For example, you are limited to a total
of 100 SOQL queries, or 300 including parent-to-child queries. The cumulative maximum
number of records returned by all SOQL queries, including parent-to-child, is 50,000.