Sign Up ›

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

Moving SOQL outside of for loop

The below code works fine.  However you will notice that I do have a SOQL inside of a loop.
In an attempt to avoid issues down the line, I need to move this outisde the loop.  Any ideas on how to do this an still reference  the current b.Candidate__c(which is the contactID attached to the current Book_of_Business__c record.     Thanks in Advance

trigger BOBChange on Book_of_Business__c (before insert, before update) {
 
List<Quintile__c> Quintiles = new List<Quintile__c> ();
List<Book_of_Business__c> bob=[SELECT Id,Name,Candidate__c  from Book_of_Business__c ];
List<Contact> Contacts = new List<Contact> ();

Quintiles = [Select Quintile__c, T12_Min__c,T12_Max__c, LOS_Min__c, LOS_Max__c
             FROM      
                Quintile__c
             WHERE
                start_date__c <= today AND
                End_date__c > today];
               
              
    If (bob.size() > 0){
        for (Book_of_Business__c b : Trigger.new) {
            system.debug('BOB ------------- > ' + b.Id);
            system.debug('CandidateID ------------- > ' + b.Candidate__c);
            Contacts = [Select ID, Quintile_LOS__c, Total_years_as_a_rep__c, Producer_LOS__c,FirstName, LastName from Contact where ID =: b.Candidate__c];                 
            for (Contact c : Contacts){
             system.debug('C  ID ------------- > ' + c.Id);
                for (Quintile__c q : Quintiles) {
                 if(b.Candidate__c == c.ID &&
                  q.T12_Min__c <= b.Post_Haircut_T_12__c &&
                        q.T12_Max__c > b.Post_Haircut_T_12__c &&
                        q.LOS_Min__c <= c.Quintile_LOS__c &&
                        q.LOS_Max__c > c.Quintile_LOS__c) {
                             b.Quintile__c = q.Quintile__c;   // update BOB Quintile Field
                       System.debug(' Q.Quintile__c --------- >' + Q.Quintile__c);
                             }
                }

            }
        }
    }
}
Best Answer chosen by law
Gigi.OchoaGigi.Ochoa
Create a Contact Map outside the loop.  The following article has some good examples of using Maps, Sets, and List to create more efficient loops and preventing SOQL/DML inside a loop
https://developer.salesforce.com/page/Apex_Code_Best_Practices


The code below shows how to use a Contact map.
trigger BOBChange on Book_of_Business__c (before insert, before update) {
 
	...
        ...
 
	
	Set<Id> contactIds = new Set<Id>();	
	for (Book_of_Business__c b : Trigger.new) {
		if(b.Candidate__c != null) contactIds.add(b.Candidate__c);
	}
	
        // Make sure there are ContactIds in the set before proceeding.
	if (contactIds.size() > 0){

		Map<Id, Contact> contactMap = new Map<Id, Contact>([Select ID, Quintile_LOS__c, Total_years_as_a_rep__c, Producer_LOS__c,FirstName, LastName from Contact where ID IN : contactIds]);
              

		for (Book_of_Business__c b : Trigger.new) {
	        	...
                        ...
	                         
			if(contactMap.containsKey(b.Candidate__c)
			{				
				for (Quintile__c q : Quintiles) {
					if(b.Candidate__c == contactMap.get(b.Candidate__c).ID && 
					q.T12_Min__c <= b.Post_Haircut_T_12__c &&
					q.T12_Max__c > b.Post_Haircut_T_12__c && 
					q.LOS_Min__c <= contactMap.get(b.Candidate__c).Quintile_LOS__c &&
					q.LOS_Max__c > contactMap.get(b.Candidate__c).Quintile_LOS__c) {
						b.Quintile__c = q.Quintile__c;   // update BOB Quintile Field
						System.debug(' Q.Quintile__c --------- >' + Q.Quintile__c);
					}
	        	        }
			}
		}
	}
}

All Answers

Shingo YamazakiShingo Yamazaki
Hello law,

Here is an example.

Set<ID> bobIds = new Set<ID>();
for (Book_of_Business__c b : Trigger.new) {
    bobIds.add(b.id);
}

List<Book_of_Business__c> bob2 = [
    SELECT
        Post_Haircut_T_12__c,
        Quintile__c,
        Candidate__r.Quintile_LOS__c,
        Candidate__r.Total_years_as_a_rep__c,
        Candidate__r.Producer_LOS__c,
        // and more fields of contact
    FROM
        Book_of_Business
    WHERE
        Id IN :bobIds
];

for (Book_of_Business__c b: bob2) {
    system.debug('BOB ------------- > ' + b.Id);
    // process...
}

One thing I couldn't quite understand is that which is "Parent" object, Book_of_Business__c or Contact.

You created the relationship field "candidate__c" on Book_of_Business__c,
so Book_of_Business__c is "Child" and Contact is "Parent".

So the variable "Contacts" is always single and you don't have to declare it as an array, right?
bob_buzzardbob_buzzard
The way to handle this is to build a set (not a list, as that will contain duplicates) of contact ids and retrieve these en-masse.  Then store them in a map keyed by contact id, and inside the loop simply retrieve the contact based on the Candidate__c id (the contact id):

Set<Id> contactIds=new Set<Id>();
for (Book_of_Business__c bob : trigger.new)
{
    contactIds.add(bob.Candidate__c);
}

Map<Id, Contact> contactsById=new Map<Id, Contact>();
contactsById.putAll([select id, Quintile_LOS__c .... where id in :contactIds]);

for (Book_of_Business__c b : trigger.new)
{
   Contact c=contactsById.get(b.Candidate__c);
    ....
}

Note that the putAll method needs to use the full SOQL query from your original code - I've used the ellipsis to save retyping.

Note also that there can only be one contact associated with the Candidate__c lookup, as it contains the id of a contact which is unique.
Gigi.OchoaGigi.Ochoa
Create a Contact Map outside the loop.  The following article has some good examples of using Maps, Sets, and List to create more efficient loops and preventing SOQL/DML inside a loop
https://developer.salesforce.com/page/Apex_Code_Best_Practices


The code below shows how to use a Contact map.
trigger BOBChange on Book_of_Business__c (before insert, before update) {
 
	...
        ...
 
	
	Set<Id> contactIds = new Set<Id>();	
	for (Book_of_Business__c b : Trigger.new) {
		if(b.Candidate__c != null) contactIds.add(b.Candidate__c);
	}
	
        // Make sure there are ContactIds in the set before proceeding.
	if (contactIds.size() > 0){

		Map<Id, Contact> contactMap = new Map<Id, Contact>([Select ID, Quintile_LOS__c, Total_years_as_a_rep__c, Producer_LOS__c,FirstName, LastName from Contact where ID IN : contactIds]);
              

		for (Book_of_Business__c b : Trigger.new) {
	        	...
                        ...
	                         
			if(contactMap.containsKey(b.Candidate__c)
			{				
				for (Quintile__c q : Quintiles) {
					if(b.Candidate__c == contactMap.get(b.Candidate__c).ID && 
					q.T12_Min__c <= b.Post_Haircut_T_12__c &&
					q.T12_Max__c > b.Post_Haircut_T_12__c && 
					q.LOS_Min__c <= contactMap.get(b.Candidate__c).Quintile_LOS__c &&
					q.LOS_Max__c > contactMap.get(b.Candidate__c).Quintile_LOS__c) {
						b.Quintile__c = q.Quintile__c;   // update BOB Quintile Field
						System.debug(' Q.Quintile__c --------- >' + Q.Quintile__c);
					}
	        	        }
			}
		}
	}
}


This was selected as the best answer
Gigi.OchoaGigi.Ochoa
remove the <strong> </strong> from the code.  I guess I can't bold sections.  :|
lawlaw
Gigi

I am now receiving the following error:

Description Resource Path Location Type
Compilation error: Invalid initial type LIST&lt;Contact&gt; for MAP&lt;Id,contact&gt; BOBChange.trigger /EAR Working/src/triggers line 20 Force.com compilation problem

On Line 15 above.    I did remove the <strong> </strong> from the code