function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Swaggy BSwaggy B 

Need help with getting a query out of a Loop

I have this trigger that i created but i am unable to figure out how to get the query out of the for loop, my trigger will run but i know it will hit some governer limits. 

trigger AcceptJobAndCreateEmployee on Candidate_Application__c (after update) {
    
    //This will be the list to add the new candidate
    List<Account> accounts = new List<Account>();

    //Query for the Account Record Types
    List<RecordType> rtypes = [Select name, id FROM RecordType WHERE sObjectType = 'Account' AND IsPersonType = True];
   
    Map<String,String> accountRecordTypes = new Map<String,String>();
        for(RecordType rt: rtypes){
            accountRecordTypes.put(rt.Name,rt.id);
        }
        
    

    for(Candidate_Application__c thisApplication: Trigger.new)
    {
        Candidate_Application__c oldApp = Trigger.oldMap.get(thisApplication.id);
        Boolean AppFalse = oldapp.Job_Accepted__c;
        Boolean AppTrue = thisApplication.Job_Accepted__c;
        
       List <Candidate__c> newCandidates = [Select id, name FROM Candidate__C WHERE id =: thisApplication.Candidate__c]; 
        
        if(!AppFalse && AppTrue)
        {            
            if(newCandidates.size() > 0){
                Account converted = new Account();
            
            
                converted.RecordTypeid = accountRecordTypes.get('Person Account');
                converted.LastName = newCandidates.get(0).Name;
                converted.Phone = newCandidates.get(0).Phone__c;
              
                
                accounts.add(converted);
                
            }
            

        }

    }

    insert accounts;
}
Best Answer chosen by Swaggy B
Brenda S FinnBrenda S Finn
Hello.

I believe to move the query out of your loop, you will need to build a set of Candidate Ids as you loop through Application objects. Only add entries to this set if your condition if (!AppFalse && AppTrue) is true. So your new code would look something like this. Only thing you may need to also add is a check to ensure that thisApplication.Candidate__c is non-null. Is that a condition you need to worry about? Hope this helps. Please let us know!
 
trigger AcceptJobAndCreateEmployee on Candidate_Application__c (after update) {
    //This will be the list to add the new candidate
    List<Account> accounts = new List<Account>();

    //Query for the Account Record Types
    List<RecordType> rtypes = [Select name, id FROM RecordType 
        WHERE sObjectType = 'Account' AND IsPersonType = True];
   
    Map<String,String> accountRecordTypes = new Map<String,String>();
    for(RecordType rt: rtypes) {
            accountRecordTypes.put(rt.Name,rt.id);
    }

    Set<Id> setCandidateIds = new Set<Id>();
    for(Candidate_Application__c thisApplication: Trigger.new)
    {
        Candidate_Application__c oldApp = Trigger.oldMap.get(thisApplication.id);
        Boolean AppFalse = oldapp.Job_Accepted__c;
        Boolean AppTrue = thisApplication.Job_Accepted__c;
       
        if(!AppFalse && AppTrue)
        {            
            setCandidateIds.put(thisApplication.Candidate__c);
        }
    }

   // now query for your Candidate by the Ids collected in for-loop above.
   // Ive not compiled this - you may need to convert Set to List for query.
   List<Candidate__c> newCandidates = [Select id, name FROM Candidate__C 
        WHERE id in :setCandidateIds]; 

    for (Candidate__c candidate : newCandidates) {
        if (setCandidateIds.get(candidate.Id)) {
           // create a new Account and add it to list for insertion
                Account converted = new Account();
                converted.RecordTypeid = accountRecordTypes.get('Person Account');
                converted.LastName = candidate.Name;
                converted.Phone = candidate.Phone__c;
                accounts.add(converted);   
            }
        }
    insert accounts;
}

 

All Answers

Anil SavaliyaAnil Savaliya
Hey,

Instead of using direct query in trigger use List,Map,and Set funtion.

Please refer below code as example,It will give you more idea,how to write code?

https://developer.salesforce.com/forums/#!/feedtype=SINGLE_QUESTION_DETAIL&dc=Apex_Code_Development&criteria=ALLQUESTIONS&id=906F0000000B0VAIA0

Regards,
Anil Savaliya
Brenda S FinnBrenda S Finn
Hello.

I believe to move the query out of your loop, you will need to build a set of Candidate Ids as you loop through Application objects. Only add entries to this set if your condition if (!AppFalse && AppTrue) is true. So your new code would look something like this. Only thing you may need to also add is a check to ensure that thisApplication.Candidate__c is non-null. Is that a condition you need to worry about? Hope this helps. Please let us know!
 
trigger AcceptJobAndCreateEmployee on Candidate_Application__c (after update) {
    //This will be the list to add the new candidate
    List<Account> accounts = new List<Account>();

    //Query for the Account Record Types
    List<RecordType> rtypes = [Select name, id FROM RecordType 
        WHERE sObjectType = 'Account' AND IsPersonType = True];
   
    Map<String,String> accountRecordTypes = new Map<String,String>();
    for(RecordType rt: rtypes) {
            accountRecordTypes.put(rt.Name,rt.id);
    }

    Set<Id> setCandidateIds = new Set<Id>();
    for(Candidate_Application__c thisApplication: Trigger.new)
    {
        Candidate_Application__c oldApp = Trigger.oldMap.get(thisApplication.id);
        Boolean AppFalse = oldapp.Job_Accepted__c;
        Boolean AppTrue = thisApplication.Job_Accepted__c;
       
        if(!AppFalse && AppTrue)
        {            
            setCandidateIds.put(thisApplication.Candidate__c);
        }
    }

   // now query for your Candidate by the Ids collected in for-loop above.
   // Ive not compiled this - you may need to convert Set to List for query.
   List<Candidate__c> newCandidates = [Select id, name FROM Candidate__C 
        WHERE id in :setCandidateIds]; 

    for (Candidate__c candidate : newCandidates) {
        if (setCandidateIds.get(candidate.Id)) {
           // create a new Account and add it to list for insertion
                Account converted = new Account();
                converted.RecordTypeid = accountRecordTypes.get('Person Account');
                converted.LastName = candidate.Name;
                converted.Phone = candidate.Phone__c;
                accounts.add(converted);   
            }
        }
    insert accounts;
}

 
This was selected as the best answer
Swaggy BSwaggy B
Thank you both for the assistants the only problem i had was that the set method does is not able to get, so i had to add it then use the contain method. 
Brenda S FinnBrenda S Finn
Great - glad you were able to get it to work. I apologize about wrong signature for the Set - I often make that mistake as i am so used to dealing with Lists.