Salesforce Developer Soql For Loop

salesforce-developer-soql

// Salesforce - Developer - SOQL - Best Practices:
// Avoid handling individual records one at a time.
// Avoid using SOQL statements inside loop.
// Bulkify helper methods.

When a batch of records initiates Apex, a single instance of that Apex code is 
executed, but it needs to handle all of the records in that given batch. For example, 
a trigger could be invoked by an Force.com SOAP API call that inserted a batch of 
records. So if a batch of records invokes the same Apex code, all of those records 
need to be processed as a bulk, in order to write scalable code and avoid hitting 
governor limits.  In fact, when Salesforce invoke our trigger, it pass all those 
records to our trigger and we must handle all those records.

Here is the optimal way to 'bulkify' the code to efficiently query the contacts 
in a single query and only perform a single update DML operation.

trigger accountTestTrggr on Account (before insert, before update) {
  // Queries all Contacts related to the incoming Account records 
  // in a single SOQL query.
  // This is also an example of how to use child relationships in SOQL
  List<Account> accountsWithContacts = [
    select id, name, (select id, salutation, description, 
      firstname, lastname, email from Contacts) 
    from Account where Id IN :Trigger.newMap.keySet()];

  List<Contact> contactsToUpdate = new List<Contact>{};
  // Use for loop to iterate through all the queried Account records 
  for(Account a: accountsWithContacts) {
    // Use the child relationships dot syntax to access the related 
    // Contacts
    for(Contact c: a.Contacts) {
         c.Description=c.salutation + ' ' + c.firstName + ' ' + c.lastname; 
         contactsToUpdate.add(c);
    }          
  }

  //Now outside the FOR Loop, perform a single Update DML statement. 
  update contactsToUpdate;
}

If we use helper methods, and they contains SOQL statements, and they 
are being invoked inside a loop, we must refactor our code so that these 
helper methods can handle batch of records, and we refactor our code to 
pass a batch of records to these helper methods.

trigger accountTrigger on Account (before delete, before insert, before update) {
  // This code efficiently queries all related Closed Lost and
  // Closed Won opportunities in a single query.
  List<Account> accountWithOpptys = [
    select id, name, (select id, name, closedate, stagename  from Opportunities  
      where accountId IN :Trigger.newMap.keySet() 
        and  (StageName='Closed - Lost' or StageName = 'Closed - Won')) 
    from Account 
    where Id IN :Trigger.newMap.keySet()];

  // Loop through Accounts
  for (Account a&nbsp;: accountWithOpptys) {
    //Loop through related Opportunities only once
    for (Opportunity o: a.Opportunities){
      if (o.StageName == 'Closed - Won') {
        System.debug('Opportunity Closed Won...do some more logic here...');
      } else if (o.StageName =='Closed - Lost') {
        System.debug('Opportunity Closed Lost...do some more logic here...');
      }
    }
  }
}

The total number of records that can be returned by SOQL queries in a request 
is 50,000. If returning a large set of queries causes you to exceed your heap 
limit, then a SOQL query for loop must be used instead. It can process 
multiple batches of records through the use of internal calls to query and 
queryMore.

For example, if the results are too large, the syntax below causes a runtime 
exception:

Account[] accts = [SELECT id FROM account];

In such cases, use a SOQL query for loop:

Account[] accts = new Account[];
for (List<Account> acct : [SELECT id, name FROM account
                            WHERE name LIKE 'Acme']) {
  accts.add(acct);
}
update accts;

Let the Force.com platform chunk your large query results into batches of 200 
records by using this syntax where the SOQL query is in the for loop 
definition, and then handle the individual datasets in the for loop logic.

With a SOQL for loop, you can include a SOQL query within a for loop. The 
results of a SOQL query can be iterated over within the loop. SOQL for loops 
use a different method for retrieving records—records are retrieved using 
efficient chunking with calls to the query and queryMore methods of the SOAP 
API. By using SOQL for loops, you can avoid hitting the heap size limit.

SOQL for loops iterate over all of the sObject records returned by a SOQL query. 
The syntax of a SOQL for loop is either:

for (variable : [soql_query]) {
    code_block
}

or

for (variable_list : [soql_query]) {
    code_block
}

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. Doing so enables you to work on 
batches of records and perform DML operations in batch, which helps avoid 
reaching governor limits.

insert new Account[]{new Account(Name = 'for loop 1'), 
                     new Account(Name = 'for loop 2'), 
                     new Account(Name = 'for loop 3')};

Integer i=0;
Integer j=0;
for (Account[] tmp : [SELECT Id FROM Account WHERE Name LIKE 'for loop _']) {
    j = tmp.size();
    i++;
}
System.assertEquals(3, j); // The list should have contained the three accounts
                       // named 'yyy'
System.assertEquals(1, i); // 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

The sObject list format executes the for loop once per returned batch of records.  
In other words, the body of the for loop is executed for each batch of 200 records.  
The statement after the SOQL for loop is not executed until all the batches had been 
processed.

List<String> templateIds = new List<String>();
List<Campaign> campaigns = [select EmailTemplate__c from Campaign where IsActive=true AND EmailTemplate__c <> null];
if (campaigns.size() >= 0) {
    Integer size = campaigns.size();
    for (Integer i = 0; i < size; i++) {
        Campaign campaign = campaigns.get(i);
        templateIds.add(campaign.EmailTemplate__c);
    }
}

List<EmailTemplate> templatesAll = new List<EmailTemplate>();
for (List<EmailTemplate> templates : [SELECT id,HtmlValue,Body FROM EmailTemplate WHERE Id IN :templateIds]) {
    Integer size = templates.size();
    System.debug('Size:' + size);
    for (Integer i = 0; i < size; i++) {
        EmailTemplate template = templates.get(i);
        System.debug('i:' + i);
        System.debug(template.HtmlValue);
        templatesAll.add(template);
    }
}

Pattern startPattern = Pattern.compile('(?mi)^\\s*<html>\\s*<head>\\s*<title>\\s*</title>\\s*</head>\\s*<body>');
Pattern endPattern = Pattern.compile('(?mi)\\s*</body>\\s*</html>\\s*$');

List<EmailTemplate> modifiedTemplates = new List<EmailTemplate>();
Integer size = templatesAll.size();
for (Integer i = 0; i < size; i++) {
    EmailTemplate template = templatesAll.get(i);
    String html = template.HtmlValue;
    Matcher startMatcher = startPattern.matcher(html);
    if (startMatcher.find()) {
        html = startMatcher.replaceFirst('');
        Matcher endMatcher = endPattern.matcher(html);
        html = endMatcher.replaceFirst('');
        System.debug('HTML:' + html);
        template.HtmlValue = html;
        modifiedTemplates.add(template);
    }
}

The above code demonstrate how to use SOQL for loop. Unfortunately it also contains regular for loop, so we will have to look closely.