+ Start a Discussion
Huy NguyenHuy Nguyen 

Too many SOQL :101

Hi Expert,

I have test class and trigger below. when I run test class it thrown too many SOQL in the trigger. Could any one help to fix it ?
Test class
@isTest(SeeAllData=true)
public with sharing class TestNonComplianceCreateController{
    static Disclosure_Claim_for_Payment__c dcp;
    
    static Non_Compliance__c nci;
    static testMethod void newTest() {
        InitialData();
        Test.SetCurrentPage(Page.NonComplianceCreate);
        
        List<RecordType> recordTypes = [SELECT Id, Name FROM RecordType Where Name='Non-Compliance' Limit 1];       
        String recordTypeId=recordTypes[0].Id;
        ApexPages.CurrentPage().GetParameters().Put('retURL', '/' + dcp.Id);
        ApexPages.CurrentPage().GetParameters().Put('what_id', dcp.Id);
              
        //System.Debug('aaa ' + str);
        //ApexPages.AddMessage(new ApexPages.Message(ApexPages.Severity.INFO, '/' + dcp.Id));
        
        ApexPages.StandardController sc = new ApexPages.StandardController(new Task());          
        NonComplianceCreateController controller = new NonComplianceCreateController(sc);
        controller.GetAllTaskFieldSet();
        controller.Entity.Non_Compliance_Id__c = nci.Id;
        controller.Save();
        
        //For Exception => not set retURL
        Test.SetCurrentPage(Page.NonComplianceCreate);
        ApexPages.CurrentPage().GetParameters().Put('RecordType', recordTypeId);
        ApexPages.StandardController sc1 = new ApexPages.StandardController(new Task());          
        NonComplianceCreateController controller1 = new NonComplianceCreateController(sc1);
        controller1.Entity.Non_Compliance_Id__c = nci.Id;
        controller1.Save();
        
        //For Test NonComplianceTrigger
        nci.Name='Updated Test';
        upsert nci;
    }
    static void InitialData()
    {
        Map<String, String> listRecordType = new Map<String,String>();
        for (RecordType r : [SELECT Id, Name FROM RecordType]) {
            listRecordType.put (r.Name, r.Id);
        }
        Account accountPerson = new Account(LastName = 'Test', FirstName='Name',RecordTypeId=listRecordType.get('Person') );
        upsert accountPerson;
        
        dcp = new Disclosure_Claim_for_Payment__c(isTemplate__c=false,RecordTypeId=listRecordType.get('Disclosure (Major Political Donor)'), Stakeholder__c = accountPerson.Id);
        upsert dcp;
        
        List<Line_Item_Category__c> listLIC = new List<Line_Item_Category__c>();
        List<Line_Item_Category__c> listlic1 = [SELECT Id, Add_Row__c, Reference_Number__c FROM Line_Item_Category__c WHERE Reference_Number__c = '1009'];
        Line_Item_Category__c lic1;
        if(listlic1.size() > 0){
             lic1 = listlic1[0];
            listLIC.add(lic1);
        }else{
            lic1 = new Line_Item_Category__c(Add_Row__c=true,Reference_Number__c='1009');
            listLIC.add(lic1);        
        }
        List<Line_Item_Category__c> listlic2 = [SELECT Id, Add_Row__c, Reference_Number__c FROM Line_Item_Category__c WHERE Reference_Number__c = '1010'];
        Line_Item_Category__c lic2;
        if(listlic2.size() > 0){
             lic2 = listlic2[0];
            listLIC.add(lic2);
        }else{
            lic2 = new Line_Item_Category__c(Add_Row__c=true,Reference_Number__c='1010');
            listLIC.add(lic2);        
        }
        //Line_Item_Category__c lic1 = new Line_Item_Category__c(Add_Row__c=true,Reference_Number__c='1009');
        //upsert lic1;
        //Line_Item_Category__c lic2 = new Line_Item_Category__c(Add_Row__c=true,Reference_Number__c='1010');
        //upsert lic2;
        system.debug('Huy test: ' + Limits.getLimitQueries());
        Line_Item_Type__c lit1 = new Line_Item_Type__c(Line_Item_Category__c=lic1.Id,Disclosure_Claim_for_Payment__c=dcp.Id);
        upsert lit1;
        system.debug('Huy test: ' + Limits.getLimitQueries());
        Line_Item_Type__c lit2 = new Line_Item_Type__c(Line_Item_Category__c=lic2.Id,Disclosure_Claim_for_Payment__c=dcp.Id);
        upsert lit2;
        system.debug('Huy test: ' + Limits.getLimitQueries());
        Line_Item__c li = new Line_Item__c(Line_Item_Type__c = lit1.Id);
        upsert li; // it thrown exception here
        
        system.debug('Huy test: ' + Limits.getLimitQueries());
        nci = new Non_Compliance__c(Name='test',Legislative_Reference__c='test',Compliance_Issue__c='test',
                                    RecordTypeId=listRecordType.get('Disclosure Non-Compliance'));
        upsert nci;
        system.debug('Huy test: ' + Limits.getLimitQueries());
    }
}

and the trigger .I just paste a part of the trigger and the bold line is thrown exception
trigger LineItemTrigger on Line_Item__c (after insert, after update, before delete, after delete, before insert, before update) {

// QC 940 update
    set<id> LineItemTypeId = new set<id>();
    for(Line_Item__c lineItem: trigger.new ){
        LineItemTypeId.add(lineItem.line_Item_Type__c );    
    }
    
    map<id, Line_Item_Type__c> lineItemcategory = new map<id,Line_Item_Type__c>( [select id,line_Item_category_text__c from Line_Item_Type__c where id in :LineItemTypeId] );
    
    Id AuditlineItemRecordTypeId = [SELECT Id FROM RecordType WHERE Name='Audit Line Item' LIMIT 1].Id;
    
    for(Line_Item__c lineItem: trigger.new ){
    string categoryText = lineItemcategory.get( lineItem.line_Item_Type__c ).line_Item_category_text__c ;
    if( trigger.isBefore && ( trigger.isinsert || trigger.isupdate )) {
            if( lineItem.RecordTypeId== AuditlineItemRecordTypeId){                            
                if( categoryText.startsWith('ECE')
                && ( categoryText.Contains('Stationery and Postage')
                || categoryText.Contains('Distribution of Election Material') )  ){
                    
                    LineItem.vouching_Advertising_material_provided__c = 'NA' ;
                    
                }
            }
        
        }
    
    }
It is used only 1 time . but it thrown exception
 
Best Answer chosen by Huy Nguyen
bob_buzzardbob_buzzard
Do you have any other triggers that fire when you insert your test data items? It suggests that the setup is consuming all of the SOQL queries prior to executing the test, and as you only look to be creating half a dozen or so objects, that implies one or more of the upserts executes triggers that aren't bulk safe.

All Answers

bob_buzzardbob_buzzard
Do you have any other triggers that fire when you insert your test data items? It suggests that the setup is consuming all of the SOQL queries prior to executing the test, and as you only look to be creating half a dozen or so objects, that implies one or more of the upserts executes triggers that aren't bulk safe.
This was selected as the best answer
Huy NguyenHuy Nguyen
I found the root cause