function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
the0nly0nethe0nly0ne 

Working around 20 SOQLs limitation? Should I learn more on Lists, Sets, and Maps?

I am new to Apex development, but for my first "real" programming language, I am picking it up quick. The only thing I have run into so far that is challenging is that I ran into a limitation: no more than 20 SOQLs per event.

 

I have several cross-object triggers that are designed to sometimes trigger each other. I believe I should be looking into Lists, Sets, and Maps to work around the only 20 SOQLs limitation. Currently, I start with Leads, automatically generating related custom objects. I then refer to these records much later in the process by using code that is similar to the following example:

 

Object o = [select field, secondObject from object where Id = lead.objectLookup__c];

secondObject so = [select field, antherField from secondObject where Id = o.secondObject__c];

 

Does anyone have some advice, a detailed introduction to Lists Maps and Sets, or example code? I have already looked at the Apex Guide, but it seems to leave out a lot of what could otherwise be very useful example code (unless, of course, I simply overlooked the example code section).

 

Thanks, in advance.

Best Answer chosen by Admin (Salesforce Developers) 
aalbertaalbert
It is important to use Apex Collections to efficiently query data and store the data in memory. A combination of using collections and streamling SOQL queries can substantially help efficiently write Apex code and avoid governor limits.

Here is a sample that inefficiently uses Collections:

trigger accountTrigger on Account (before delete, before insert, before update) {
 
    //This code inefficiently queries the Opportunity object in two seperate queries
    List<Opportunity> opptysClosedLost = [select id, name, closedate, stagename
            from Opportunity where 
            accountId IN :Trigger.newMap.keySet() and StageName='Closed - Lost'];
   
    List<Opportunity> opptysClosedWon = [select id, name, closedate, stagename
            from Opportunity where 
            accountId IN :Trigger.newMap.keySet() and StageName='Closed - Won'];
   
    for(Account a : Trigger.new){
       
        //This code inefficiently has two inner FOR loops
        //Redundantly processes the List of Opportunity Lost
        for(Opportunity o: opptysClosedLost){   
            if(o.accountid == a.id)
               System.debug('Do more logic here...');
        }
       
        //Redundantly processes the List of Opportunity Won
        for(Opportunity o: opptysClosedWon){   
            if(o.accountid == a.id)
               System.debug('Do more logic here...');
        }
    }
}
 

The main issue with the previous snippet is the unnecessary querying of the Opportunity records in two separate queries. Use the power of the SOQL Where clause to query all data needed in a single query. Also try to use relationships if objects are related. Secondly, the above example has two inner FOR loops that redundantly loop through the list of Opportunity records just trying to find the ones related to a specific Account. 
 
This revised sample only executes 1 query for all related Opportunities and only have 1 inner FOR loop to apply the same logic, but in a much more efficient, governor-friendly manner. 
 
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 only once
    for(Account a : 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...');
    	 	}
    	 }
    
   }
}