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
Jeremiah CochranJeremiah Cochran 

Move SOQL out of For Loop


0down votefavorite
I have 2 custom objects we will call ObjectOne__c and ObjectTwo__c, these 2 objects DO NOT have a direct relationship to each other. However, I have a business need that requires fields on ObjectTwo__c to be updated to match the fields on ObjectOne__c when ObjectOne__c is updated. Here is a sample of the Trigger that handles this process. My issues is I need to move the SOQL out of the loop to properly handle mass updates, but I can’t figure out how to do that and still get the trigger to properly update all the records in the trigger.
Here is a sample of the code in my trigger: 
trigger COSyncTrigger on ObjectOne__c (after update) { 
//Create list to store the ObjectTwo__c records that will be Updated 
List < ObjectTwo__c> upObTwo = new List < ObjectTwo__c >(); 

//Look at records being Updated 
if(trigger.isUpdate){ for(ObjectOne__c ObjOne : trigger.new){ 

//Create a list of existing ObjectTwo__c records that match the ObjectOne__c record being updated 
List < ObjectTwo__c > checkupObjTwo = [Select Id from ObjectTwo__c Where (Account__c = :ObjOne.Account__c And Owner = : ObjOne.Owner And Client__c = : ObjOne. Client__c) ]; 

//If the Above List is NOT Empty Update the ObjectTwo__c records 
If(!checkupObjTwo.isEmpty()) { 

ObjectTwo__c upobjtwo = [Select Id from ObjectTwo__c Where (Account__c = : ObjOne.Account__c And Owner = : ObjOne.Owner And Client__c = : ObjOne. Client__c)]; 

upobjtwo.Client_Role__c = ObjOne. Client_Role__c; 
upobjtwo.Client_Status__c = ObjOne. Client_Status__c; 

//Add the ATM record to the list to be updated later 
upObTwo.add(upobjtwo); } 
} 

//If the List of records to update is NOT Empty update the ObjectTwo__c records 
if(upObTwo.size() > 0){ 
system.debug('Count of ObjectTwo__c records to update = '+ upObTwo.size()); 

update upObTwo; } 
}
I have done some research and understand I need to create a list outside my loop but I can't seem to get it to update ObjectTwo__c records to Match the ObjectOne__c records in the trigger. Please explain how I can accomplish this and provide an example if possible. Thank you in advance.
Best Answer chosen by Jeremiah Cochran
Jeremiah CochranJeremiah Cochran

A user on Salesforce Stack Exchange actually provided me with the solutions I needed. It can be viewed at the link: https://salesforce.stackexchange.com/questions/218155/remove-soql-from-for-loop

Here is the code that worked for me:
 

trigger COSyncTrigger on ObjectOne__c (after update) { 
// Aggregate data to query
Set<Id> accountIds = new Set<Id>(), 
        ownerIds = new Set<Id>(),
        clientIds = new Set<Id>();
for(ObjectOne__c record: Trigger.new) {
    accountIds.add(record.Account__c);
    ownerIds.add(record.OwnerId);
    clientIds.add(record.Client__c);
}
Map<ObjectTwo__c, ObjectTwo__c> objTwos = new Map<ObjectTwo__c, ObjectTwo__c>(),
     updates = new Map<ObjectTwo__c, ObjectTwo__c>();
// Query for existing data
for(ObjectTwo__c record: [SELECT Account__c, OwnerId, Client__c FROM ObjectTwo__c
       WHERE Account__c = :accountIds AND OwnerId = :ownerIds 
             AND Client__c = :clientIds]) {
    objTwos.put(
        new ObjectTwo__c(Account__c=record.Account__c, 
            OwnerId=record.OwnerId, 
            Client__c=record.Client__c),
        record);
}
// Update the records
for(ObjectOne__c record: Trigger.new) {
    ObjectTwo__c key = new ObjectTwo__c(
        Client__c=record.Client__c, OwnerId=record.OwnerId, Account__c=record.Account__c),
         objTwoRecord = objTwos.get(key);
    if(objTwoRecord != null) {
        updates.put(key, objTwoRecord);
        objTwoRecord.Client_Role__c = record.Client_Role__c;
        objTwoRecord.Client_Status__c = record.Client_Status__c;
    }
}
update updates.values();
}

All Answers

GhanshyamChoudhariGhanshyamChoudhari
trigger COSyncTrigger on ObjectOne__c (after update) { 
//Create list to store the ObjectTwo__c records that will be Updated 
List < ObjectTwo__c> upObTwo = new List < ObjectTwo__c >(); 
List < String> holdowner = new List < String >(); 
List < String> holclient = new List < String >(); 

//Look at records being Updated 
if(trigger.isUpdate){ 
for(ObjectOne__c ObjOne : trigger.new){ 
holdowner.add(ObjOne.Owner.Id);
holclient.add(ObjOne. Client__r.Id)
} 
}
List < ObjectTwo__c > checkupObjTwo = [Select Id from ObjectTwo__c Where (Account__c = :ObjOne.Account__c And Owner.Id IN = :holdowner And Client__r.Id In = : holclient) ]; 

//If the Above List is NOT Empty Update the ObjectTwo__c records 
If(!checkupObjTwo.isEmpty()) { 

ObjectTwo__c upobjtwo = [Select Id from ObjectTwo__c Where (Account__c = : ObjOne.Account__c And Owner = : ObjOne.Owner And Client__c = : ObjOne. Client__c)]; 

upobjtwo.Client_Role__c = ObjOne. Client_Role__c; 
upobjtwo.Client_Status__c = ObjOne. Client_Status__c; 

//Add the ATM record to the list to be updated later 
upObTwo.add(upobjtwo); } 

}

 
Gururaj BGururaj B
Hi Jeremiah, 
I hope this below code should help you to avoid SOQL inside the loop. If this works or help you in any way please mark this as best answer. Thank you. 
trigger COSyncTrigger on ObjectOne__c (after update) {
List < ObjectTwo__c> upObTwo = new List < ObjectTwo__c >();
list<id> AccIdLst = new list<id>();
if(trigger.isUpdate){
for(ObjectOne__c ObjOne : trigger.new){
AccIdLst.add(ObjOne.Account__c );
}
}

List < ObjectTwo__c > checkupObjTwo = [Select Id from ObjectTwo__c Where Account__c in:AccIdLst]; 

If(!checkupObjTwo.isEmpty()) { 
for(ObjectOne__c ObjOne : trigger.new){

for(ObjectTwo__c objtwo:checkupObjTwo){
if(objtwo.Account__c=ObjOne.Account__c && objtwo.Owner=ObjOne.Owner && objtwo.Client__c=ObjOne.Client__c)
{
objtwo.Client_Role__c = ObjOne. Client_Role__c; 
objtwo.Client_Status__c = ObjOne. Client_Status__c; 
upObTwo.add(objtwo);
}
}
}
}

if(upObTwo.size() > 0){ 
system.debug('Count of ObjectTwo__c records to update = '+ upObTwo.size()); 

update upObTwo; }

}

 
Jeremiah CochranJeremiah Cochran

A user on Salesforce Stack Exchange actually provided me with the solutions I needed. It can be viewed at the link: https://salesforce.stackexchange.com/questions/218155/remove-soql-from-for-loop

Here is the code that worked for me:
 

trigger COSyncTrigger on ObjectOne__c (after update) { 
// Aggregate data to query
Set<Id> accountIds = new Set<Id>(), 
        ownerIds = new Set<Id>(),
        clientIds = new Set<Id>();
for(ObjectOne__c record: Trigger.new) {
    accountIds.add(record.Account__c);
    ownerIds.add(record.OwnerId);
    clientIds.add(record.Client__c);
}
Map<ObjectTwo__c, ObjectTwo__c> objTwos = new Map<ObjectTwo__c, ObjectTwo__c>(),
     updates = new Map<ObjectTwo__c, ObjectTwo__c>();
// Query for existing data
for(ObjectTwo__c record: [SELECT Account__c, OwnerId, Client__c FROM ObjectTwo__c
       WHERE Account__c = :accountIds AND OwnerId = :ownerIds 
             AND Client__c = :clientIds]) {
    objTwos.put(
        new ObjectTwo__c(Account__c=record.Account__c, 
            OwnerId=record.OwnerId, 
            Client__c=record.Client__c),
        record);
}
// Update the records
for(ObjectOne__c record: Trigger.new) {
    ObjectTwo__c key = new ObjectTwo__c(
        Client__c=record.Client__c, OwnerId=record.OwnerId, Account__c=record.Account__c),
         objTwoRecord = objTwos.get(key);
    if(objTwoRecord != null) {
        updates.put(key, objTwoRecord);
        objTwoRecord.Client_Role__c = record.Client_Role__c;
        objTwoRecord.Client_Status__c = record.Client_Status__c;
    }
}
update updates.values();
}
This was selected as the best answer
Gururaj BGururaj B
I dont think in the solution that you mentioned is going to pick the correct record from the object "ObjectTwo__c ". Any combination of Account__c,Client_Role__c ,Client_Status__c  will be picked and updated. I dont think that is what you would want. Please refer my solution where it exactly looks for the cobination of all the 3 fields of object, "ObjectOne__c" on the object,"ObjectTwo__c" to pick and update.
Jeremiah CochranJeremiah Cochran
@Gururaj B, Thanks for your input. But actually, the solution noted above works perfectly because if you only want Object_Two__c where the 3 key fragments are either "a, b, c" or "x, y, z", moving the query outside of the loop will give you records with "a, b, c", "a, b, z", ..., "x, y, c", "x, y, z" (8 total possible combinations, if they exist). Thus, the query alone is insufficient, so you need to do some filtering with Apex which is exactly what code in the answer above does. I have implemented it and tested it and thus far it is working flawlessly. Hope this helps clarify things.
Gururaj BGururaj B
I am not refering to the combination with in those 3 fields. For example if you have 3 records updated on object_one__c that triggers this code. Assume the values of those 3 fields of object_one__c as:
a,b,c
p,q,r
x,y.z
As per the code now it picks all those records from Object_two__c that matches the field combination of a,q,r / a,q,z / p,b,z / p,q,z / x,b,r/.......etc..
You can try this scenrio and you will come to know what i am saying
Jeremiah CochranJeremiah Cochran

@Gururaj B, That is correct. This code will initially pick up ALL ObjectTwo__c records with any combination of the 3 values and assign them to the MAP. As specified in this line of code:

Map<ObjectTwo__c, ObjectTwo__c> objTwos = new Map<ObjectTwo__c, ObjectTwo__c>(),
     updates = new Map<ObjectTwo__c, ObjectTwo__c>();

// Query for existing data
for(ObjectTwo__c record: [SELECT Account__c, OwnerId, Client__c FROM ObjectTwo__c
       WHERE Account__c = :accountIds AND OwnerId = :ownerIds
             AND Client__c = :clientIds]) {
    objTwos.put(
        new ObjectTwo__c(Account__c=record.Account__c,
            OwnerId=record.OwnerId,
            Client__c=record.Client__c),
        record);

}

But later we ensure that only the ObjectTwo__c records that match ObjectOne__c are actually updated by using this line of code:

// Update the records
for(ObjectOne__c record: Trigger.new) {
    
ObjectTwo__c key = new ObjectTwo__c(
        Client__c=record.Client__c, 
        OwnerId=record.OwnerId, 
        Account__c=record.Account__c),
        
objTwoRecord = objTwos.get(key);

    if(objTwoRecord != null) {
        updates.put(key, objTwoRecord);
        objTwoRecord.Client_Role__c = record.Client_Role__c;
        objTwoRecord.Client_Status__c = record.Client_Status__c;
    }
}
update updates.values();
This looks at the MAP and then only pulls the ObjectTwo__c record(s) that exactly match the ObjectOne__c record in the current loop