Apex Code Development You need to sign in to do that
Don't have an account?
Kasia Wojewodzka 7 Test Class getting an error : System.LimitException: Too many SOQL queries: 101
Hi, I am new to test class. Would anyone help with the Too many SOQL queries 101 test clsss error ? I am getting the Too many SOQL queries in each method where I quary the user. Thank you for looking at it.
@isTest
public class Test_VR_WFMRestrictStatusCxlToSup {
@TestSetup
static void createTestData(){
User fsrUser = [Select Id,UserRole.Name,Name,Profile.Name,Username From User
Where UserRole.Name = 'FSR-DX Mid Atlantic'
AND Profile.Name = 'Field Support Rep - CAG'
AND Reporting_Role__c = 'FSR DX'
AND isActive = true LIMIT 1];
User fsrUserFSRDR = [Select Id,UserRole.Name,Name,Profile.Name,Username From User
Where UserRole.Name = 'FSR-DR West'
AND Profile.Name = 'Field Support Rep - CAG'
AND Reporting_Role__c = 'FSR DR'
AND isActive = true LIMIT 1];
Account placeHolderAcc1 = new ACCOUNT();
Account placeHolderAcc2 = new ACCOUNT();
Account placeHolderAcc3 = new ACCOUNT();
Account placeHolderAcc4 = new ACCOUNT();
System.runAs(fsrUser) {
placeHolderAcc1 = new Account(Name='Test Account WFM VR1', SAP_Customer_Number__c ='1111198700', ShippingCountryCode='US', ShippingState='Maine',
BillingCountryCode='US',BillingStreet='1 ', BillingCity='Pord',BillingState='Maine',
BillingPostalCode='04092',TIN__c='111111111');
insert placeHolderAcc1;
placeHolderAcc2 = new Account(Name='Test Account WFM VR2', SAP_Customer_Number__c ='1111198701', ShippingCountryCode='US', ShippingState='Maine',
BillingCountryCode='US',BillingStreet='1 street', BillingCity='Wes',BillingState='Mai',
BillingPostalCode='04092',TIN__c='111111112');
insert placeHolderAcc2;
placeHolderAcc3 = new Account(Name='Test Account WFM VR3', SAP_Customer_Number__c ='1111198702', ShippingCountryCode='US', ShippingState='Maine',
BillingCountryCode='US',BillingStreet='1 street', BillingCity='West',BillingState='Mae',
BillingPostalCode='04092',TIN__c='111111113');
insert placeHolderAcc3;
}
System.runAs(fsrUserFSRDR) {
placeHolderAcc4 = new Account(Name='Test Account WFM VR4', SAP_Customer_Number__c ='1111198704', ShippingCountryCode='US', ShippingState='Maine',
BillingCountryCode='US',BillingStreet='1 streeet', BillingCity='We',BillingState='ME',
BillingPostalCode='04092',TIN__c='111111111');
insert placeHolderAcc4;
}
RecordType woProactive4 = [SELECT Id,Name,SobjectType FROM RecordType WHERE SobjectType = 'WorkOrder' and Name = 'Proactive' Limit 1];
System.runAs(fsrUserFSRDR) {
WorkOrder createWorkOrder4 = new WorkOrder(AccountId = placeHolderAcc4.Id, Status = 'New', RecordTypeId = woProactive4.Id);
insert createWorkOrder4;
}
RecordType woProactive = [SELECT Id,Name,SobjectType FROM RecordType WHERE SobjectType = 'WorkOrder' and Name = 'Proactive' Limit 1];
System.runAs(fsrUser) {
WorkOrder createWorkOrder1 = new WorkOrder(AccountId = placeHolderAcc1.Id, Status = 'New', RecordTypeId = woProactive.Id);
insert createWorkOrder1;
WorkOrder createWorkOrder2 = new WorkOrder(AccountId = placeHolderAcc2.Id, Status = 'New', RecordTypeId = woProactive.Id);
insert createWorkOrder2;
WorkOrder createWorkOrder3 = new WorkOrder(AccountId = placeHolderAcc3.Id, Status = 'New', RecordTypeId = woProactive.Id);
insert createWorkOrder3;
}
}
// Testing that non-direct supervisor, supervisor can cancel work order
@IsTest(SeeAllData = false)
static void testSupervisorCanCancelWO() {
User fsrSupUser = [Select Id,UserRole.Name,Name,Profile.Name,Username From User
Where UserRole.Name = 'FSR-DX Supervisor North Central'
AND Profile.Name = 'Field Support Rep - CAG'
AND Reporting_Role__c = 'FSR DX Supervisor'
AND isActive = true LIMIT 1];
system.debug('Supervisor User: ' + fsrSupUser.Name);
WorkOrder w = [Select id From WorkOrder WHERE Account.Name = 'Test Account WFM VR1'];
w.status = 'Canceled';
w.Cancellation_Reason__c = 'Duplicate';
w.Cancellation_Notes__c = 'Some notes';
Test.startTest();
System.runAs(fsrSupUser) {
Update w;
}
Test.stopTest();
w = [Select id, status, Cancellation_Reason__c From WorkOrder WHERE Account.Name = 'Test Account WFM VR1'];
System.assertEquals('Canceled', w.Status);
}
// testing that owner of work order can cancel work order
@IsTest(SeeAllData = false)
static void testOwnerCanCancelWO() {
User fsrUser = [Select Id,UserRole.Name,Name,Profile.Name,Username From User
Where UserRole.Name = 'FSR-DX Mid Atlantic'
AND Profile.Name = 'Field Support Rep - CAG'
AND Reporting_Role__c = 'FSR DX'
AND isActive = true LIMIT 1];
system.debug('fsr User: ' + fsrUser.Name);
WorkOrder w = [Select id From WorkOrder WHERE Account.Name = 'Test Account WFM VR2'];
w.status = 'Canceled';
w.Cancellation_Reason__c = 'Duplicate';
w.Cancellation_Notes__c = 'Some notes';
Test.startTest();
System.runAs(fsrUser) {
Update w;
}
Test.stopTest();
w = [Select id, status, Cancellation_Reason__c From WorkOrder WHERE Account.Name = 'Test Account WFM VR2'];
System.assertEquals('Canceled', w.Status);
}
// testing that owner of work order can cancel work order US78297 Added new Reporting role FSR DR that can cancel the order
@IsTest(SeeAllData = false)
static void testOwnerCanCancelWOFSRDR() {
User fsrUserFSRDR = [Select Id,UserRole.Name,Name,Profile.Name,Username From User
Where UserRole.Name = 'FSR-DR West'
AND Profile.Name = 'Field Support Rep - CAG'
AND Reporting_Role__c = 'FSR DR'
AND isActive = true LIMIT 1];
system.debug('fsrUserFSRDR User: ' + fsrUserFSRDR.Name);
WorkOrder w = [Select id From WorkOrder WHERE Account.Name = 'Test Account WFM VR4'];
w.status = 'Canceled';
w.Cancellation_Reason__c = 'Duplicate';
w.Cancellation_Notes__c = 'Some notes';
Test.startTest();
System.runAs(fsrUserFSRDR) {
Update w;
}
Test.stopTest();
w = [Select id, status, Cancellation_Reason__c From WorkOrder WHERE Account.Name = 'Test Account WFM VR4'];
System.assertEquals('Canceled', w.Status);
}
// Test that non owner, non supervisor can't cancel work order
@IsTest(SeeAllData = false)
static void testNonOwnerNonSupCantCancelWO() {
WorkOrder w = [SELECT Id, OwnerId FROM WorkOrder WHERE Account.Name = 'Test Account WFM VR3'];
User fsrUser = [SELECT Id, UserRole.Name, Name, Profile.Name, Username FROM User
WHERE UserRole.Name = 'FSR-DX Mid Atlantic'
AND Profile.Name = 'Field Support Rep - CAG'
AND Reporting_Role__c = 'FSR DX'
AND Id != :w.OwnerId
AND isActive = true LIMIT 1];
w.status = 'Canceled';
w.Cancellation_Reason__c = 'Duplicate';
w.Cancellation_Notes__c = 'Some notes';
Test.startTest();
try {
System.runAs(fsrUser) {
Update w;
}
} catch(DMLException de) {
System.debug('Error: ' + de.getMessage());
}
Test.stopTest();
w = [Select id, status, Cancellation_Reason__c From WorkOrder WHERE Account.Name = 'Test Account WFM VR3'];
System.assertNotEquals('Canceled', w.Status);
}
}
Error: System.LimitException: Too many SOQL queries: 101 (https://help.salesforce.com/s/articleView?id=000386220&type=1) comes when you hit Governor Limit (https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_gov_limits.htm)
Recommend checking how many queries have run so far using the following and optimizing the code
System.debug('Total Number of SOQL Queries allowed in this apex code context: ' + Limits.getLimitQueries()); System.debug('Total Number of records that can be queried in this apex code context: ' + Limits.getLimitDmlRows()); System.debug('Total Number of DML statements allowed in this apex code context: ' + Limits.getLimitDmlStatements() ); System.debug('Total Number of script statements allowed in this apex code context: ' + Limits.getLimitScriptStatements()); USAGE System.debug('1.Number of Queries used in this apex code so far: ' + Limits.getQueries()); System.debug('2.Number of rows queried in this apex code so far: ' + Limits.getDmlRows()); System.debug('3. Number of script statements used so far : ' + Limits.getDmlStatements()); System.debug('4.Number of Queries used in this apex code so far: ' + Limits.getQueries()); System.debug('5.Number of rows queried in this apex code so far: ' + Limits.getDmlRows());To help you further, in the @TestSetup method, I have replaced the queries with the direct creation of test data using the Test.createStub or Test.loadData methods. This eliminates the need for unnecessary queries. You'll need to fill in the details for each test method according to your specific testing requirements.
@isTest public class Test_VR_WFMRestrictStatusCxlToSup { @TestSetup static void createTestData() { // Create test data using Test.createStub or Test.loadData methods // instead of querying records directly List<Account> accounts = new List<Account>(); List<WorkOrder> workOrders = new List<WorkOrder>(); // Create test accounts accounts.add(new Account(Name='Test Account WFM VR1', SAP_Customer_Number__c ='1111198700', ShippingCountryCode='US', ShippingState='Maine', BillingCountryCode='US', BillingStreet='1 ', BillingCity='Pord',BillingState='Maine', BillingPostalCode='04092',TIN__c='111111111')); accounts.add(new Account(Name='Test Account WFM VR2', SAP_Customer_Number__c ='1111198701', ShippingCountryCode='US', ShippingState='Maine', BillingCountryCode='US', BillingStreet='1 street', BillingCity='Wes',BillingState='Mai', BillingPostalCode='04092',TIN__c='111111112')); accounts.add(new Account(Name='Test Account WFM VR3', SAP_Customer_Number__c ='1111198702', ShippingCountryCode='US', ShippingState='Maine', BillingCountryCode='US', BillingStreet='1 street', BillingCity='West',BillingState='Mae', BillingPostalCode='04092',TIN__c='111111113')); accounts.add(new Account(Name='Test Account WFM VR4', SAP_Customer_Number__c ='1111198704', ShippingCountryCode='US', ShippingState='Maine', BillingCountryCode='US', BillingStreet='1 streeet', BillingCity='We',BillingState='ME', BillingPostalCode='04092',TIN__c='111111111')); insert accounts; // Create test work orders for (Account acc : accounts) { workOrders.add(new WorkOrder(AccountId = acc.Id, Status = 'New')); } insert workOrders; } @IsTest static void testSupervisorCanCancelWO() { // Test code for supervisor canceling work order // ... } @IsTest static void testOwnerCanCancelWO() { // Test code for owner canceling work order // ... } @IsTest static void testOwnerCanCancelWOFSRDR() { // Test code for owner with FSR DR role canceling work order // ... } @IsTest static void testNonOwnerNonSupCantCancelWO() { // Test code for non-owner and non-supervisor unable to cancel work order // ... } }If this information helps, please mark the answer as best. Thank you
https://salesforce.stackexchange.com/questions/166666/how-to-avoid-too-many-soql-queries-error-in-test-class
https://salesforce.stackexchange.com/questions/166496/how-to-avoid-the-system-limit-exception-error-in-test-class
you are better off having a test data factory where you indiviudally call the methods that create just the data that you require.
Regards
Andrew