0

How could I move this SOQL query out of the following for loop? I'd like to test if there is a shared field between the Lead records and any Account records.

public static void SOQLoutOfFor(List<Lead> leads){
  for(Lead l: leads){
    List<Account> accts = [SELECT Id from Account where Custom_Field__c = :l.Custom_Field__c]
    if(accts.size() > 0){
      //something
    }
  }
}

2 Answers 2

1

I can spell out the steps here :

  1. You have not provided the data type of Custom_Field__c. but if it is a finite set you can form a list of those values:

    e.g. for(Lead l: leads){ 
               listofval.add(l.Custom_Field__c); //listofval maybe a list of string declared outside the loop
               }
    
  2. Outside the loop:

    // Get all Account records in one shot.

List<Account> accts = [SELECT Id from Account where Custom_Field__c IN :listofval]

1
  • 1
    ah gotcha - so I'll need another loop... I'm thinking I may need to use Map<Custom_Field__c_Type,List<Id>> Jun 19, 2020 at 13:46
0

For my use case I ended up going with a Map, so that I could perform the if statement:

public static void fn(List<Lead> leads){
        Set<String> customFields = returnSetOfCustomField(leads);
        Map<String, Id> customField_to_Account = returnMapOfCustomFieldToAccount(customFields);
        
        for(Lead l: leads){
            if(customField_to_Account.containsKey(l.Custom_Field__c)){
                // Something
            }
        }
}

//Helper functions
private static Set<String> returnSetOfCustomField(List<Lead> leads){
        Set<String> result = new Set<String>();
        for(Lead l : leads){
            result.add(l.Custom_Field__c);
        }
        return result;
    }
private static Map<String, Id> returnMapOfCustomFieldToAccount(Set<String> customFields){
    Map<String, Id> result = new Map<String, Id>();
    for(Account a : [Select Id, Custom_Field__c FROM Account where Custom_Field__c IN :customFields]){
        result.put(a.Custom_Field__c, a.Id);
    }
    return result;
}
2
  • 1
    Cool that worked for you :)
    – user40322
    Jun 19, 2020 at 21:31
  • 1
    If Custom_Field__c in not unique in your Account object, you might have an issue. The map will only store the 'later' account per custom field. Jun 20, 2020 at 0:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .