Using SOQL for Loops

With SOQL for loop,you can include a SOQL query within a for loop. SOQL for loops iterate over all of the sObject records returned by a SOQL query.

Syntax-

for (variable : [soql_query]) {
// code_block
}

for (variable_list : [soql_query]) {
// code_block
}

Both variable and variable_list must be of the same type as the sObjects that are returned by the soql_query.

It is preferable to use the sObject list format of the SOQL for loop as the loop executes once for each batch of 200 sObjects.

Difference between SOQL For Loops  & Standard SOQL Queries

SOQL for loops differ from standard SOQL statements because of the method they use to retrieve sObjects. The standard SOQL Queries can retrieve either the count of a query or a number of object records, SOQL for loops retrieve all sObjects, using efficient chunking with calls to the query and queryMore methods of the SOAP API.

To avoid hitting the heap size limit error, you should always use a SOQL for loop to process large record set.

NOTE – Queries including an aggregate function don’t support queryMore. A run-time exception occurs if you use a query containing an aggregate function that returns more than 2,000 rows in a for loop.

SOQL For Loop Fomrats –

SOQL for loops can process records one at a time using a single sObject variable, or in batches of 200 sObjects at a time using an sObject list:

  • The single sObject format executes the for loop’s <code_block> once per sObject record. Each DML statement ends up processing only one sObject at a time.
  • The sObject list format executes the for loop’s <code_block> once per list of 200 sObjects. Each DML statement can bulk process a list of sObjects at a time.

// Create a savepoint because the data should not be committed to the database
Savepoint sp = Database.setSavepoint();

insert new Account[]{new Account(Name = 'yyy'), 
 new Account(Name = 'yyy'), 
 new Account(Name = 'yyy')};

// The single sObject format executes the for loop once per returned record
Integer i = 0;
for (Account tmp : [SELECT Id FROM Account WHERE Name = 'yyy']) {
 i++;
}
// Since there were three accounts named 'yyy' in the
// database, the loop executed three times
System.debug('Integer Value = '+i);

// The sObject list format executes the for loop once per returned batch
// of records
i = 0;
Integer j;
for (Account[] tmp : [SELECT Id FROM Account WHERE Name = 'yyy']) {
 j = tmp.size();
 i++;
}
// The list should have contained the three accounts named 'yyy'
System.debug('@@@ Value if j = '+j);
// Since a single batch can hold up to 200 records and,
// only three records should have been returned, the 
// loop should have executed only once
System.debug('### Value of i = '+i);

// Revert the database to the original state
Database.rollback(sp);

NOTE – 

DML statements can only process up to 10,000 records at a time, and sObject list for loops process records in batches of 200.

You might get a QueryException in a SOQL for loop with the message Aggregate query has too many rows for direct assignment, use FOR loop.

Example –  The query in the following SOQL for loop retrieves child contacts for a particular account. If this account contains more than 200 child contacts, the statements in the for loop cause an exception.

for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts) FROM Account                                      WHERE Id IN (‘<ID value>’)]) {                                                                                    List<Contact> contactList = acct.Contacts; // Causes an error                                                        Integer count = acct.Contacts.size(); // Causes an error
}

To avoid getting this exception, use a for loop to iterate over the child records, as follows.

for (Account acct : [SELECT Id, Name, (SELECT Id, Name FROM Contacts)
FROM Account WHERE Id IN (‘<ID value>’)]) {
Integer count=0;
for (Contact c : acct.Contacts) {
count++;
}
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s