You need to sign in to do that
Don't have an account?
Soql limit issue
Hi,
I am facing an issue like "too many sqol queries : 101" when i run a test class.
I will post my code here:
trigger updateassreqwon on Requirement__c (after update)
{
if(Trigger.isUpdate)
{
string reqId = Trigger.old[0].Id;
Requirement__c listR = [Select Id, Name, job_title__c, won__c from Requirement__c where Id =:reqId limit 1];
// List<RequirementAssignment__c> listreq = [Select Id, Name, won__c, Lead_Recruiter__c, Recruiter__c,Requirement__c from RequirementAssignment__c where requirement__c=:reqId limit 1];
// System.Debug('listreq :' +listreq);
for(Requirement__c req:Trigger.old)
{
Requirement__c R = new Requirement__c(won__c = listR.won__c);
List<RequirementAssignment__c> RAA = new List<RequirementAssignment__c>();
{
for(RequirementAssignment__c pc: [Select Id, Name, won__c, Lead_Recruiter__c, Recruiter__c,Requirement__c from RequirementAssignment__c where requirement__c=:reqId limit 1])
{
System.Debug('pc.Lead_Recruiter__c :' + pc.Lead_Recruiter__c);
if(pc.Lead_Recruiter__c == null)
{
System.Debug('pc.Lead_Recruiter__c :' + pc.Lead_Recruiter__c);
user up = [Select u.Id from User u where (u.Profile.Name ='Lead Recruiter' or u.Profile.Name ='LR Chatter Only User') and Isactive = true limit 1];
pc.Lead_Recruiter__c = up.id;
}
if(R.won__c == true)
pc.won__c = 'Yes';
else
pc.won__c = 'No';
System.Debug('pc.won__c :' + pc.won__c);
RAA.add(pc);
}
if(RAA.size() >0 )
{
Database.DMLOptions dml = new Database.DMLOptions();
// dml.optAllOrNone = false;
database.update(RAA,dml);
}
} // if listreq.size Ends
}
}
}
In this trigger, i am facing soql limit error in for(RequirementAssignment__c pc: [Select Id, Name, won__c, Lead_Recruiter__c, Recruiter__c,Requirement__c from RequirementAssignment__c where requirement__c=:reqId limit 1]) line.
trigger updateresumessubmitted on Candidate_Mapping__c (after insert, after update)
{
integer ressubmitted;
integer ressubmitted1;
string UserId = UserInfo.getUserId();
System.Debug('UserId =' + UserId);
if(Trigger.isUpdate || Trigger.isInsert)
{
for(Candidate_Mapping__c cm : Trigger.new)
{
system.debug('cm:' + cm);
List<Requirement__c> reqlist = [Select Id, Submitted_Resumes__c, status__c,cumulative_resumes_submitted__c from requirement__c where id =:cm.requirement__c limit 1];
system.debug('reqlist :' +reqlist);
//List <AggregateResult> ar = [SELECT count(Id) cnt FROM Candidate_Mapping__c where (Status1__c != 'rejected' and Status1__c != 'LR Rejected' ) and requirement__c =:cm.requirement__c];
//List <Candidate_Mapping__c> ar = [SELECT Id FROM Candidate_Mapping__c where (Status1__c != 'rejected' and Status1__c != 'LR Rejected' ) and requirement__c =:cm.requirement__c limit 1];
// system.debug('ar:' +ar);
for(AggregateResult ar1 : [SELECT count(Id) cnt FROM Candidate_Mapping__c where (Status1__c != 'rejected' and Status1__c != 'LR Rejected' ) and requirement__c =:cm.requirement__c])
{
ressubmitted = Integer.valueOf(ar1.get('cnt'));
system.debug('ressubmitted :' +ressubmitted);
for(requirement__c req : reqlist)
{
req.cumulative_resumes_submitted__c = ressubmitted;
system.debug('cumulative_resumes_submitted__c :' + req.cumulative_resumes_submitted__c);
system.debug('status__c12 :'+ req.status__c);
if(req.status__c == 'Open')
{
system.debug('status__c123 :'+ req.status__c);
for(integer i = 0 ; i< reqlist.size(); i++)
{
reqlist[i].Submitted_Resumes__c = ressubmitted;
}
}
if(req.status__c == 'Re-open' )
{
system.debug('status__c1 :'+ req.status__c);
system.debug('cm.Status1__c11 :'+ cm.Status1__c);
system.debug('submitted_resumes :' + req.Submitted_Resumes__c);
system.debug('cm.LRdate__c :' + cm.LRdate__c);
if(cm.status1__c == 'applied' && cm.createdbyid == UserId || cm.status1__c == 'LR Approved' && cm.createdbyid == UserId || cm.status1__c == 'Approved' && cm.createdbyid == UserId )
{
system.debug('status__c00:'+ req.status__c);
req.Submitted_Resumes__c = req.Submitted_Resumes__c + 1;
system.debug('submitted_resumes0 :' + req.Submitted_Resumes__c);
}
else if (cm.status1__c == 'LR Rejected' && cm.createdbyid == UserId && req.Submitted_Resumes__c == 0 || cm.status1__c == 'Rejected' && cm.createdbyid == UserId && req.Submitted_Resumes__c == 0)
{
system.debug('status__c 1123:'+ req.status__c);
req.Submitted_Resumes__c = 0;
system.debug('submitted_resumes3 :' + req.Submitted_Resumes__c);
}
else if (cm.status1__c == 'rejected' || cm.status1__c == 'LR Rejected')
{
// system.debug('cm.LRdate__c :' + cm.LRdate__c);
system.debug('status__c 1122:'+ req.status__c);
req.Submitted_Resumes__c = req.Submitted_Resumes__c - 1;
system.debug('submitted_resumes2 :' + req.Submitted_Resumes__c);
}
else
{
system.debug('status__c 1121:'+ req.status__c);
req.Submitted_Resumes__c = req.Submitted_Resumes__c;
system.debug('submitted_resumes1 :' + req.Submitted_Resumes__c);
}
}
system.debug('cm.Status1__c :'+ cm.Status1__c);
system.debug('status__c :'+ req.status__c);
system.debug('submitted_resumes3 :' + req.Submitted_Resumes__c);
upsert reqlist;
system.debug('reqlist1:' +reqlist);
}
}
}
}
}
In this trigger, i am facing soql limit error in for(AggregateResult ar1 : [SELECT count(Id) cnt FROM Candidate_Mapping__c where (Status1__c != 'rejected' and Status1__c != 'LR Rejected' ) and requirement__c =:cm.requirement__c].
When i run the below test class i am facing error in any one of the above 2 stmts.
@isTest
private class testclassCandidateMapfromCandidatesEdit
{
static testMethod void myUnitTest()
{
String oppid;
String cid;
String Userid;
Integer i,j,k;
Id id1 = UserInfo.getUserRoleId();
UserRole test1 = [Select Name from UserRole where id = :id1 limit 1];
system.test.starttest();
Profile p = [SELECT Id FROM Profile where Name = 'R Chatter Only User' ];
userRole r=[select id from userrole where Name = 'Recruiter' ];
User u = new User(Alias = 'testa', Email='standarduser1@testorg.com',
EmailEncodingKey='UTF-8', LastName='Testaa', LanguageLocaleKey='en_US',
LocaleSidKey='en_US', ProfileId = p.Id,
TimeZoneSidKey='America/Los_Angeles', UserName='standarduser1@testorg.com.ezredev',userRoleid=r.id);
System.runAs(u)
{
Candidate_Mapping__c coa =new Candidate_Mapping__c();
coa.status1__c = 'Applied';
coa.Employer_s_Authorization__c = 'test';
coa.LR_Comments__c = 'test by lr';
coa.MR_Comments__c = 'test by MR';
coa.Requirement_Owner_Email__c = 'testOwner@preludesys.com';
coa.Manager_Email_ID__c = 'testOwner1@preludesys.com';
coa.R_Comments__c = '';
coa.LR_Status__c = 'Rejected';
coa.MR_Status__c = 'Applied';
coa.LR_Status_Date__c = DateTime.now().format('MM-dd-yyyy hh:mm a z', 'America/Los_Angeles');
coa.MR_Status_Date__c = DateTime.now().format('MM-dd-yyyy hh:mm a z', 'America/Los_Angeles');
coa.Submitted_to_Client__c = 'Yes';
coa.Interview_Scheduled__c = 'Yes';
coa.Interview_Accepted__c = 'Yes';
coa.Client_Interviewed__c = 'Yes';
coa.Client_Offered__c = 'Yes';
coa.Candidate_started__c = 'Yes';
coa.Comments__c = '';
candidate__c cc1=new candidate__c();
cc1.Name='test';
cc1.Last_name__c='testoneo';
cc1.Current_city__c='chennai';
cc1.Current_state__c='Maine';
cc1.Gender__c='Male';
cc1.Employer_Name__c='ttt';
cc1.Email__c='test@gmail.com';
cc1.interview_contact_phone__c='Mobile Phone';
cc1.Contract_type__c='Corp to Corp';
cc1.visa_type__c='H1 B';
cc1.cost__c =100;
cc1.mobile_phone_no__c='9890043500';
cc1.employer_contact_email__c='test@gmail.com';
cc1.Employer_Contact_Name_Name__c='tt';
cc1.Employer_Mobile_No__c='9988009876';
cc1.Employer_Name__c = 'jos';
cc1.Employer_Work_Phone_No__c = '(425) 264-6771';
cc1.Followup_Date__c =date.today();
insert cc1;
coa.Candidate__c=cc1.id;
system.debug('candid:'+cc1.id);
requirement__c rc1=new requirement__c();
rc1.Name='SampathReq';
rc1.Job_Title__c='.net';
rc1.Duration__c='6';
rc1.No_Of_Resumes__c=2;
rc1.Min_Cost__c=100;
rc1.Max_Cost__c=200;
rc1.Rate__c=200;
rc1.Rate_Basis__c='Hourly';
rc1.Status__c='open';
rc1.Position_Type__c='Contract';
rc1.State__c='CA';
rc1.City__c='CA';
rc1.Est_Start_Date__c=date.today();
rc1.won__c = true;
insert rc1;
coa.Requirement__c=rc1.id;
RequirementAssignment__c oppa=new RequirementAssignment__c();
oppa=new RequirementAssignment__c();
oppa.Requirement__c=rc1.Id;
oppa.Recruiter__c =u.Id;
oppa.won__c = 'yes';
System.Debug('U:'+ oppa.Recruiter__c);
insert oppa;
insert coa;
List<ContentVersion> cv = new List<ContentVersion>();
cv = [Select Id, Candidate_ID__c From ContentVersion where Candidate_ID__c =:coa.Candidate__c limit 1] ;
System.Debug('CV : '+ cv);
if(cv.Size()>0)
{
for(ContentVersion cv1 : cv)
{
Candidate__c can = new Candidate__c(Id = cc1.Id);
can.Resume_Attach__c = '0584000000003RrAAI';
update can;
}
}
CandidateMapfromCandidatesEdit cc = new CandidateMapfromCandidatesEdit();
cc.save();
apexpages.currentpage().getparameters().put('cid' , coa.id);
cc.save();
system.assert(coa!= null);
cc.cancel();
cc.save();
}
Profile p1 =[SELECT Id From Profile where Name= 'LR Chatter Only User'];
userRole r1 =[SELECT Id FROM UserRole where Name = 'Lead Recruiter'];
User u1 = new User(Alias = 'testuser', Email='testUser@testorg.com',
EmailEncodingKey='UTF-8', LastName='TestUseraa', LanguageLocaleKey='en_US',
LocaleSidKey='en_US', ProfileId = p1.Id,
TimeZoneSidKey='America/Los_Angeles', UserName='testUser@testorg.com.ezredev',userRoleid=r1.id);
System.runAs(u1){
Candidate_Mapping__c coa1 =new Candidate_Mapping__c();
coa1.status1__c = 'Applied';
coa1.Employer_s_Authorization__c = 'test';
coa1.LR_Comments__c = 'test by lr';
coa1.MR_Comments__c = 'test by MR';
coa1.Requirement_Owner_Email__c = 'testOwn1@preludesys.com';
coa1.Manager_Email_ID__c = 'testOwn1@preludesys.com';
coa1.R_Comments__c = '';
coa1.LR_Status__c = 'Rejected';
coa1.MR_Status__c = 'Applied';
coa1.LR_Status_Date__c = DateTime.now().format('MM-dd-yyyy hh:mm a z', 'America/Los_Angeles');
coa1.MR_Status_Date__c = DateTime.now().format('MM-dd-yyyy hh:mm a z', 'America/Los_Angeles');
coa1.Submitted_to_Client__c = 'Yes';
coa1.Interview_Scheduled__c = 'Yes';
coa1.Interview_Accepted__c = 'Yes';
coa1.Client_Interviewed__c = 'Yes';
coa1.Client_Offered__c = 'Yes';
coa1.Candidate_started__c = 'Yes';
coa1.Comments__c = '';
candidate__c cc1=new candidate__c();
cc1.Name='testtwo';
cc1.last_name__c='testtwo';
cc1.current_city__c='chennai';
cc1.current_state__c='Maine';
cc1.gender__c='Male';
cc1.Employer_Name__c='ttt';
cc1.email__c='test@gmail.com';
cc1.interview_contact_phone__c='Mobile Phone';
cc1.contract_type__c='w2';
cc1.visa_type__c='H1 B';
cc1.cost__c =100;
cc1.mobile_phone_no__c='9890043500';
cc1.employer_contact_email__c='test@gmail.com';
cc1.Employer_Contact_Name_Name__c='tyt';
cc1.Employer_Mobile_No__c='9988009876';
insert cc1;
coa1.Candidate__c=cc1.id;
system.debug('candid:'+cc1.id);
requirement__c rc1=new requirement__c();
rc1.Name='SampathReq0';
rc1.Job_Title__c='.net0';
rc1.Duration__c='6';
rc1.No_Of_Resumes__c=2;
rc1.Min_Cost__c=100;
rc1.Max_Cost__c=200;
rc1.Rate__c=200;
rc1.Rate_Basis__c='Hourly';
rc1.Status__c='open';
rc1.Position_Type__c='Contract';
rc1.State__c='CA';
rc1.City__c='CA';
rc1.Est_Start_Date__c=date.today();
rc1.won__c = true;
insert rc1;
coa1.Requirement__c=rc1.id;
RequirementAssignment__c oppa=new RequirementAssignment__c();
oppa=new RequirementAssignment__c();
oppa.Requirement__c=rc1.Id;
oppa.lead_recruiter__c =u1.Id;
oppa.won__c = 'yes';
System.Debug('U:'+ oppa.lead_recruiter__c);
insert oppa;
insert coa1;
List<ContentVersion> cv = new List<ContentVersion>();
cv = [Select Id, Candidate_ID__c From ContentVersion limit 1] ;
if(cv.Size()>0)
{
for(ContentVersion cv1 : cv)
{
Candidate__c can = new Candidate__c(Id = cv1.Id);
//can.Cost__c = 0;
can.Resume_Attach__c = cv1.Id;
update can;
}
}
CandidateMapfromCandidatesEdit cc = new CandidateMapfromCandidatesEdit();
cc.save();
apexpages.currentpage().getparameters().put('cid' , coa1.id);
cc.save();
system.assert(coa1!= null);
cc.cancel();
cc.save();
}
system.test.stoptest();
}
}
Some times i am facing issue in the first trigger and some times i am facing this issue in 2nd trigger.
Kindly advice.
Hi Abinaya,
Please try the below code. Hope, it will solve your issue.
All Answers
By moving the SOQL queries and DML operations out of the For Loops you should not run into a governor limit using this style.
Hi,
There are following problems in your code.
1. A common mistake is that queries are placed inside a for loop.There is a governor limit that enforces a maximum number of SOQL queries.When queries are placed inside a for loop, a query is executed on each iteration and governor limit is easily reached.Instead, move the SOQL query outside of the for loop and retrieve all the necessary data in a single query as following.
2. Always bulkify your code i.e write your code for 200 records not for a single record.
3. Never perform DML operation in for loop as you have done.
Please find below code for your first trigger and also modify your second trigger like this.
trigger updateassreqwon on Requirement__c (after update)
{
Map<String,RequirementAssignment__c> reqAssMap = new Map<String,RequirementAssignment__c>;
user up = [Select u.Id from User u where (u.Profile.Name ='Lead Recruiter' or u.Profile.Name ='LR Chatter Only User') and Isactive = true limit 1];
List<RequirementAssignment__c> listreq = [Select Id, Name, won__c, Lead_Recruiter__c, Recruiter__c,Requirement__c from RequirementAssignment__c where requirement__c IN :Trigger.OldMapKeySet()];
List<RequirementAssignment__c> RAA = new List<RequirementAssignment__c>();
for(RequirementAssignment__c reqAss : listreq )
{
reqAssMap.put(reqAss.requirement__c,reqAss);
}
for(Requirement__c req :Trigger.old)
{
if(reqAssMap.containsKey(req.Id) {
if( reqAssMap.get(req.Id).Lead_Recruiter__c == null)
{
reqAssMap.get(req.Id).Lead_Recruiter__c = up.id;
}
if(req.won__c == true){
reqAssMap.get(req.Id).won__c = 'Yes';
}else {
reqAssMap.get(req.Id).won__c = 'No';
}
RAA.add(reqAssMap.get(req.Id));
}
}
if(RAA.size() >0 )
{
Database.DMLOptions dml = new Database.DMLOptions();
// dml.optAllOrNone = false;
database.update(RAA,dml);
}
}
/**If this post helps you then please mark it as a solution and don't forget to give me kudo's.***/
Thanks
www.grazitti.com
hi,
I have changed the above trigger but i am facing issue in this trigger in line no 11 same too many soql queries 101
trigger trigCandidateMapping on Candidate_Mapping__c (after update, after insert)
{
Candidate_Mapping__c cm = Trigger.new[0];
if(Trigger.isInsert || Trigger.isUpdate)
{
string contentId = Trigger.new[0].Id;
// List<Candidate_Mapping__c> listCM =new List<Candidate_Mapping__c>();
// listCM = [Select Candidate__c,Requirement__c from Candidate_Mapping__c where Id =:contentId limit 1];
// System.Debug('listCM - Insert' + listCM);
for (Candidate_Mapping__c cm1 : [Select Candidate__c,Requirement__c from Candidate_Mapping__c where Id =:contentId limit 1])
{
List<ContentVersion> listR = new List<ContentVersion>();
listR = [Select Id,Candidate_ID__c from ContentVersion where Candidate_Id__c =:cm1.Candidate__c];
System.Debug('listR - Insert'+ listR);
for(ContentVersion cv1 : listR)
{
//if(cv1.Candidate_ID__c == cm1.Candidate__c)
//{
Candidate__c can = new Candidate__c(Id = cv1.Candidate_ID__c);
can.Resume_Attach__c = cv1.Id;
system.debug('can.Resume_Attach__c - Trigger Insert' + can.Resume_Attach__c);
update can;
// }
}
}
}
}
Hi Abinaya,
Please try the below code. Hope, it will solve your issue.