0

In the following method:

public List<Id> getLeadIds(Lead[] listOfLeads) {
    List<Id> leadIds = new List<Id>();
    for (Lead lead : listOfLeads) {
        if (lead.Company != null && lead.Rating != null) {
            List <Account> listOfAccounts = [SELECT Name FROM Account WHERE Account.Name = :lead.Company AND Account.Rating = :lead.Rating AND Account.Status__c != 'Cancelled'];
            if (listOfAccounts.size() > 0) {
                leadIds.add(lead.id);
            }
        }
    }
    return leadIds;
}

Method getLeadIds is invoked every time when new Lead records are created. Each Lead is compared to all NOT cancelled Account records and checked for duplicates by Account name and Account rating. The issue I have is that the SOQL query is inside the for loop which is giving me: Too many SOQL queries error message. How to implement the getLeadIds method where the SOQL query is outside the for loop?

2
  • 2
    This requirement appears at first blush to be implementable without code using Salesforce's native Duplicate Management feature. Have you considered doing so?
    – David Reed
    Jun 23, 2019 at 23:34
  • 1
    Duplicate Rules do exactly this without using code. You can directly call the dupe rules & get duplicate data returned. Your root problem is calling soql inside of a loop. For each record you make at least one query, you'll hit your limit quickly this way. Jun 24, 2019 at 15:00

1 Answer 1

-2

I think you want to gather your info to build your account query ahead of time and then run your filters on the accounts in the database vs. leads in memory. So, something like this:

public Set<Id> getLeadIds(Lead[] leads) {

    Map<Id, Lead> id2Lead = new Map<Id, Lead>(leads);
    Set<String> companies = new Set<String>();
    Set<String> ratings = new Set<String>();

    Set<Id> leadIds = new Set<Id>();
    for(Lead lRec : id2Lead.values()){
        companies.add(lRec.Company);
        ratings.add(lRec.Rating);
    }

    List<Account> accts = [
        SELECT      Name, Rating
        FROM        Account    
        WHERE       Name IN :companies AND
                    Status__c != 'Cancelled' AND
                    Rating IN :ratings
        ORDER BY    Name ASC, Rating ASC
    ];


    for(Lead lRec : id2Lead.values()){
        for(Account acct : accts){
            if (
                lRec.Company != null &&
                lRec.Rating != null &&
                lRec.company.equalsIgnoreCase(acct.Name) &&
                lrec.Rating  == acct.Rating
            ){
                leadIds.add(lRec.Id);
                break;
            }
        }
    }
    return leadIds;

}

Having said that, this method checks against accounts in your database.

Said another way, s'pose you have n Leads in this transaction with the same company name, rating info, etc/ then both those n leads would be returned by this method. is that ok, or are you handling that use case elsewhere?

EDIT: Alternative implementation.

 public Set<Id> getLeadIds(Lead[] leads) {

    Map<Id, Lead> id2Lead = new Map<Id, Lead>(leads);
    Set<String> companies = new Set<String>();
    Set<String> ratings = new Set<String>();

    Set<Id> leadIds = new Set<Id>();
    for(Lead lRec : id2Lead.values()){
        companies.add(lRec.Company);
        ratings.add(lRec.Rating);
    }

    Set<LeadAcctMatch> acctInfoSet = new Set<LeadAcctMatch>(); 

    for(Account acct : [
        SELECT      Name, Rating
        FROM        Account    
        WHERE       Name IN :companies AND
                    Status__c != 'Cancelled' AND
                    Rating IN :ratings
        ORDER BY    Name ASC, Rating ASC
    ]){
        acctInfoSet.add(new LeadAcctMatch(acct.Name.toUpperCase() , acct.Rating.toUpperCase()));
    }


    for(Lead lRec : id2Lead.values()){
        if( lRec.Company != null && lRec.Rating != null){

            LeadAcctMatch theLead = new LeadAcctMatch(lRec.Company.toUpperCase(), lRec.Rating.toUpperCase());
            if(acctInfoSet.contains(theLead)){
                leadIds.add(lRec.Id);
            }
        }
    }
    return leadIds;
}

class LeadAcctMatch{
    String company;
    String rating;

    LeadAcctMatch(String company, String rating){
        this.company = company;
        this.rating = rating;
    }
}
6
  • Thank you for your suggestion PartOfTheOhana. It is vital however to avoid nested for loops. Is that possible and how to do that? Please advise.
    – sfdcadmdev
    Jun 23, 2019 at 21:16
  • The SOQL is not nested. .. Jun 23, 2019 at 21:18
  • Yes indeed. You are correct. But it is really important for me to avoid nested for loops. Please advise how it can be achieved?
    – sfdcadmdev
    Jun 23, 2019 at 21:22
  • Yeah, I can do that. but it would add additional complexity. What's the reasoning to avoid that? Jun 23, 2019 at 21:24
  • I realize that, however by avoiding nested loops I strive to achieve better code maintainability and efficiency by reducing Apex CPU time limits.
    – sfdcadmdev
    Jun 23, 2019 at 21:39

You must log in to answer this question.

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