+ Start a Discussion

help me on changing this class

hi all,


how to avoid the soql stmts inside the for loop in this class. plz help me.


public class assigncandidatestatusemailalert

public void SendEmail()

List<UserRole> UsersRoleList = new List<UserRole>([Select Id, Name from UserRole where Name = 'Vice President' or Name = 'Business Development Manager' or Name ='Management Representative' or Name ='Business Development Executive']);

System.debug('UserList123:' + UsersRoleList );

for(UserRole UsersRoleList123 :UsersRoleList)
List<User> UsersList = new List<User>([ SELECT Id, Name, Email FROM User where IsActive = true and UserRoleId = : UsersRoleList123.Id]);

System.debug('userlist1234' + UsersList);

for ( User userlist1 : UsersList )
List<Candidate_Mapping__c> candidatemappinglist = new List<Candidate_Mapping__c>( [SELECT Candidate__r.Candidate_Full_Name__c,Requirement__r.Name,Requirement__r.Job_Title__c, Requirement__r.Opportunity_Code__c, Requirement__r.CreatedDate,status1__c,Req_Owner_Email__c,Requirement_Owner__c FROM Candidate_Mapping__c where Submitted_to_Client__c ='' and status1__c = 'approved' and LastModifiedBy.Id = : userlist1.Id and LastModifiedDate = Last_n_days : 14]);

system.debug('candidatemappinglist :' + candidatemappinglist );

String htmlBody ;

Messaging.SingleEmailMessage mail;

htmlBody ='Dear '+userlist1 .Name+',</br></br>';

htmlBody =htmlBody +'<table style="border: 1px solid black;border-collapse:collapse;"><tr><td style="border: 1px solid black;"><b>Candidate</b> </td><td style="border: 1px solid black;"><b>Requirement</b> </td><td style="border: 1px solid black;"><b>Requirement Code</b></td><td style="border: 1px solid black;"><b>Client Name</b></td><td style="border: 1px solid black;"><b>Requirement Created Date</b></td></tr>';

for (Candidate_Mapping__c cm :candidatemappinglist )
htmlBody = htmlBody + '<tr><td style="border: 1px solid black;">'+cm .Candidate__r.Candidate_Full_Name__c+'</td><td style="border: 1px solid black;">'+cm.Requirement__r.Name+'</td><td style="border: 1px solid black;">'+cm.Requirement__r.Opportunity_Code__c+'</td><td style="border: 1px solid black;">'+cm.Requirement__r.Job_Title__c+'</td><td style="border: 1px solid black;">'+cm.Requirement__r.CreatedDate+'</td></tr>';


htmlBody = htmlBody +'</table></br>Either it is yet to be submitted to the client or the details are not updated in the EZRE system.</br> Please close this action item asap. </br></br>Regards</br>EZRE Team';

mail = new Messaging.SingleEmailMessage();

String[] address = new String[]{userlist1.Email};
mail.setHtmlBody(htmlBody );
mail.setSubject('Submitted to client alert');
String[] bccaddress = new String[]{'abinaya_s@preludesys.com'};
mail.saveAsActivity = false;
if( candidatemappinglist.size() > 0 )

Messaging.sendEmail(new Messaging.SingleEmailMessage[] { mail });


Best Answer chosen by Admin (Salesforce Developers) 


1. Create a  list of id.In this case ,I have create userlstid and added all id from in a list from a loop.

Best way for you is  when you are adding user in userlist ,on the same loop you can use userlstid to collect all id.

2. Use the list of id in soql. You have to use as IN 

List userlstid=new List();
for ( User userlist1 : UsersList )
{ userlstid.add(userlist1.id); }
List<Candidate_Mapping__c> candidatemappinglist = new List<Candidate_Mapping__c>( [SELECT Candidate__r.Candidate_Full_Name__c, Requirement__r.Name,Requirement__r.Job_Title__c, Requirement__r.Opportunity_Code__c, Requirement__r.CreatedDate,status1__c,Req_Owner_Email__c, Requirement_Owner__c FROM Candidate_Mapping__c where Submitted_to_Client__c ='' and status1__c = 'approved' and LastModifiedBy.Id IN : userlstid and LastModifiedDate = Last_n_days : 14]);