3

I have a loop that needs to make two SOQL calls as follows:

List<Lead> updatedownload= new List<Lead>{};
u = [SELECT Name,Id,Date_of_first_contact__c,Previous_user__c,  Download__c, Qualification__c, Final_Email_Date__c, Email,FirstName, LastName, Company,Pass_Fail__c,OwnerID FROM Lead  WHERE User_Processed__c = False];
for (Lead inquery :u ){
aa = [SELECT Id, Name FROM Account WHERE Name = :inquery.Company];
f(aa.size() > 0){
trainee  = [select Email__c, Last_Name__c,First_Name__c,Institution__c, Host_Name from Trainee__c where email__c=:inquery.email ];
if(trainee.size() > 0){
                    if(newcontact.size() == 0) {
                      contact = new Contact();
                      contact.FirstName  = inquery.FirstName;
                      contact.LastName   = inquery.LastName;
                      contact.Email      = inquery.email;
                      contact.AccountId  =  aa.get(0).ID;
                      try {
updatedownload.add(inquery);
                         } catch (DMLException e) {
                           ApexPages.addMessage(new ApexPages.message(ApexPages.severity.ERROR,'Error creating new contact'));
                           return null;
                         }
                    }
}
}


 insert updatedownload; // inserts the new record into the database

Now this all works fine,

but I need to run this through more than 100 Leads,

so I have two SOQL calls inside the loop that will cause me to reach the governor limits.

So here I have three databases: Leads, Accounts, and a custom database called Trainee__c.

How can I change the code to make only 1 SOQL call for all the Leads, their respective Accounts and their respective Trainee__c information?

Especially that I should only retrieve the accounts and Trainee__c that actually do exist (i.e. Not Null) .

2
  • Can you please explain why you need the Trainee__c data? From what I can see you're only checking if there are 1 or more trainee records with the same email address as the Lead. Is that correct? Commented Dec 20, 2013 at 1:33
  • Well actually i have 7 more pieces of data [columns] i read, but i left it out here, just to make the code more readable for this form. Also this is just part of the code, i left out a lot of of code. Commented Dec 20, 2013 at 2:26

1 Answer 1

8

In general, when SOQL does not provide a solution to join two SObjects (like Lead and Account), you need to do individual queries and save the results in a map (pass 1); then in pass 2, use for loops to process the Leads results and look up in the other maps for matching values.

Here is an excerpt:

// Pass 1
Set<String> aSearchSet = new Set<String>();
Set<String> tSearchSet = new Set<String> ();
List<Lead> lList = [select id, email, company from Lead];
for (Lead l : lList) {
  aSearchSet.add(l.company);
  tSearchSet.add(l.email);
}

Map<String,Account> companyToAccountMap = new Map<String,Account>();
Map<String,Trainee__c> emailToTraineeMap = new Map<String,Trainee__c>();
for (Account a: [select id, name from Account where name IN :aSearchSet])
    companyToAccountMap.put(a.name,a);
for (Trainee__c t : [select Email__c, Last_Name__c,First_Name__c,Institution__c, Host_Name from Trainee__c where email IN :tSearchSet])
     emailToTraineeMap.put(t.email,t);

// Pass 2 - at this point you have done only 3 SOQL queries and saved 
// the Leads in a list, the Accounts in a map keyed by l.company,
// the Trainees in a map keyed by l.email

for (Lead l : lList) {
  if (companyToAccountMap.containsKey(l.company)
    if (emailToTraineeMap.containsKey(l.email) {
      // do your work here ...I see your code example is an excerpt of something larger 
      // if you are going to do DML, you need to add everything into a list and do the 
      // DML in one statement outside of any for loop
}
7
  • This looks like what i need, i will need to do some testing, Commented Dec 20, 2013 at 2:29
  • Im getting the following error: Compile Error: expecting a map literal assignment, found '}' at Map<String,Account> companyToAccountMap = new Map<String,Account> {[select id, name from Account where name IN :aSearchSet]}; Commented Dec 20, 2013 at 15:48
  • oops, my bad, used wrong syntax when I typed into browser; syntax fixed.
    – cropredy
    Commented Dec 20, 2013 at 18:28
  • Can you please tell me how do i find a list of leads, that do not match any company in my org? So the command line you gave me is great, it will process all the companies that exist in my organization, but how do i find the opposite? Commented Dec 20, 2013 at 18:32
  • Ok i figured out a way using a loop Commented Dec 20, 2013 at 19:55

You must log in to answer this question.

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