Friday, October 5, 2018

SOQL List vs SOQL For Loop

To understand the syntax of (soql for loop) and (list for loop).Let's update contact phone by using (list for loop) and (soql for loop).

Case 1)

(list for loop):

list<contact> conList=new list<contact>();
list<contact> conListUpdate=new list<contact>();
conList=[Select id,phone from contact limit 1];

for(contact obj:conList){
obj.phone='111';
conListUpdate.add(obj);
}
update conListUpdate;

Case 2)

(soql for loop)

list<contact> conList=new list<contact>();
list<contact> conListUpdate=new list<contact>();

for(contact obj:[Select id,phone from contact limit 1]){
obj.phone='111';
conListUpdate.add(obj);
}
update conListUpdate;

Now,What is the difference between SOQL List vs SOQL For Loop ?

(soql for loop) retrieves all sObjects using a call to query and queryMore whereas (list for loop) retrieves a number of objects records.

It is advisable to use (soql for loop) over (list for loop) to avoid heap size limit error.

The total number of records that can be queried by soql queries is 50,000 record. If we query more than 50,000 record we exceeds the heap limit.

//A runtime exception is thrown if this query returns enough records to exceed your heap limit.

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

To avoid this we should use SOQL query for loop it can process multiple batches of record using call to query and query more.

for (List<Account> acct : [SELECT id, name FROM account

                            WHERE name LIKE 'Test']) {
    // Your logic here

}


Note: When querying parent-child data in (soql for loop) we may get below error(Aggregate query has too many rows for direct assignment).This exception occurs when there are a large number of child records (200 or more number of records).To avoid this we should use for loop inside for loop.

Syntax:

Case 1) Will give error.....

for(Account obj:[Select id,(select id,name from contacts) from account limit 1]){
list<contact> conList=obj.contacts;  // Error here if more than 200 contacts
}


Case 2)Correct way of handling....

for(Account obj:[Select id,(select id,name from contacts) from account limit 1]){
for(contact obj1:obj.contacts)
{
//some operations
}

}
**************************************************************************************************************
SOQL List vs SOQL For Loop ,SOQL List for loop,SOQL For Loop,SOQL For Loop Syntax,SOQL list For Loop Syntax.
**************************************************************************************************************

No comments:

Post a Comment