0

Even when I think that I have bulkified my code, it is still throwing a SOQL 101 Limit Exception on the code below on Line 5 (For loop One). What I am doing Wrong?

UserIds is just a list of Oppportunties.OwnerIds

global static map<Id, Contact> getEmployeeInfos(list<Id> userIds)
{
       map<Id, Contact> employeeContacts = new map<Id, Contact>();

       for (Contact c : [SELECT Name, User__c, User__r.IsActive, LOB__c, Email, Phone,LOB__r.Short_Name__c, LOB__r.Name FROM Contact WHERE User__c IN :userIds])
       {
            employeeContacts.put(c.User__c, c);
       }

       return employeeContacts;

Complete scenario:

Failed Test Class: OpportunityFeeCalculationTest

Failed Class Methods: 1. test 2. testOverFirstAUM

Class: OpportunityFeeCalculationTest

    @isTest
    public static void test()
    {        
        CoreDataTest.getRelationships();

        Test.startTest();


        Opportunity_Fee_Schedule__c f1 = new Opportunity_Fee_Schedule__c(
            Opportunity__c=CoreDataTest.Opportunities[0].id,
            Tier_Description__c='First',
            Tier_AUM_Level__c=100000,
            Tier_Fee_Bps__c=33
        );

        Opportunity_Fee_Schedule__c f2 = new Opportunity_Fee_Schedule__c(
            Opportunity__c=CoreDataTest.Opportunities[0].id,
            Tier_Description__c='Second',
            Tier_AUM_Level__c=300000,
            Tier_Fee_Bps__c=43
        );

        Opportunity_Fee_Schedule__c f3 = new Opportunity_Fee_Schedule__c(Opportunity__c=CoreDataTest.Opportunities[0].id,Tier_Description__c='Thereafter',Tier_Fee_Bps__c=33);
        insert f1;
        insert f2;
        insert f3;

        list<Opportunity> lista=new list<Opportunity>();
        lista.add(CoreDataTest.Opportunities[0]);

        OpportunityFeeCalculation_Core.calculateManagementFees(lista);       
    }  


    @isTest
    public static void testOverFirstAUM()
    {        
        CoreDataTest.getRelationships();

        list<Opportunity_Fee_Schedule__c> scheds = new list<Opportunity_Fee_Schedule__c> {
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='First',
                Tier_AUM_Level__c=300000,
                Tier_Fee_Bps__c=33
            )
            ,
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='Second',
                Tier_AUM_Level__c=100000,
                Tier_Fee_Bps__c=43
            )
            ,
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='Fourth',
                Tier_AUM_Level__c=900000,
                Tier_Fee_Bps__c=43
            )
            ,
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='Third',
                Tier_AUM_Level__c=100000,
                Tier_Fee_Bps__c=43
            )
            ,
            new Opportunity_Fee_Schedule__c(
                Opportunity__c=CoreDataTest.Opportunities[0].id,
                Tier_Description__c='Thereafter',
                //Tier_AUM_Level__c=100,
                Tier_Fee_Bps__c=3
            )

        };        

        Test.startTest();        
        insert scheds;        

        CoreDataTest.Opportunities[0].Assets__c = 2500000;
        update CoreDataTest.Opportunities[0];

        list<Opportunity> lista=new list<Opportunity>{
            CoreDataTest.Opportunities[0]
        };        

        OpportunityFeeCalculation_Core.calculateManagementFees(lista);   
    }
}

error: System.LimitException: Too many SOQL queries: 101

Stack Trace: Class.UtilitiesContact.getEmployeeInfos: line 96, column 1 Class.OpportunityTriggerHandler_Core.beforeUpdate: line 232, column 1 Trigger.OpportunityBeforeUpdate_Core: line 5, column 1


Trigger.OpportunityBeforeUpdate_Core:

trigger OpportunityBeforeUpdate_Core on Opportunity (before update) {

    if (UtilitiesSObject.RunTrigger('OpportunityBeforeUpdate_Core'))
    {
Line 5: new OpportunityTriggerHandler_Core().beforeUpdate(trigger.new, trigger.newMap, trigger.oldMap);
    }
}

Class.OpportunityTriggerHandler_Core.beforeUpdate

public void beforeUpdate(list<Opportunity> triggerNew,map<Id, Opportunity> triggerNewMap,map<Id, Opportunity> triggerOld)
{
    if (UtilitiesUser.UserContactInfo.Service_Account__c == false)
    {
        calculateManagementFees(triggerNew,triggerold);
        calculateProbability(triggerNew, triggerOld);

        map<string,boolean> keys = new map<string,boolean>();//lob+account
        set<id> lobs = new set<id>();
        set<id> companies = new set<id>();
        set<id> oppsAffected = new set<id>();
        list<id> ownerids = new list<id>();
        set<id> oppids = new set<id>();

        for (Opportunity o : triggerNewMap.values())
        {
            ownerids.add(o.ownerid);

            o.Restricted__c=o.Restricted_Formula__c;
            o.Platinum_Client_Group__c=o.Platinum_Client_Group_Formula__c;

            keys.put(o.LOB_Owner__c+'-'+o.Accountid,false);
            keys.put(o.LOB_Owner__c+'-'+o.Record_Keeper__c,false);
            keys.put(o.LOB_Owner__c+'-'+o.Consultant__c,false);
            lobs.add(o.LOB_Owner__c);
            companies.add(o.Accountid);
            companies.add(o.Record_Keeper__c);
            companies.add(o.Consultant__c);
            oppids.add(o.id);
        }   

        for(Company_LOB_Relationship__c clr:[
            select id,Company__c,LOB__c,Opt_Out__c 
            from Company_LOB_Relationship__c 
            where  LOB__c in :lobs
            AND Company__c IN :companies
        ])
        {
            string key = clr.LOB__c+'-'+clr.Company__c;
            if(keys.get(key)!=null)
            {
                keys.put(key,clr.Opt_Out__c);
            }        
        }

//Set Owner LOB
Line 232: map<Id, Contact> employeesByUser = UtilitiesContact.getEmployeeInfos(ownerids);

Class.UtilitiesContact.getEmployeeInfos: line 96, column 1

global static map<Id, Contact> getEmployeeInfos(list<Id> userIds)
    {
        map<Id, Contact> employeeContacts = new map<Id, Contact>();

Line 96: for (Contact c : [SELECT Name, User__c, User__r.IsActive, LOB__c, Email, Phone,LOB__r.Short_Name__c, LOB__r.Name FROM Contact WHERE User__c IN :userIds])
        {
            employeeContacts.put(c.User__c, c);
        }

        return employeeContacts;
    }
  • Are you getting this error when you execute a test class or on a trigger or when? Some context would help to understand the situation. – Alexander Aeons Torn Jun 10 '15 at 7:23
  • 2
    the error could exist , for example, due to a trigger context in which getEmployeeInfos() is called more than 100 times. Could you provide some context informations? – user9959 Jun 10 '15 at 7:26
2

Your code snippet does not contain enough information to figure out your issue. The SOQL 101 error is thrown when 101 SOQL statements are executed in a single request. It does not mean that the statement the error was thrown on was executed 101 times, it only means that it was the 101st SOQL statement. There could be another piece of code that ran before the code you have presented and that code executed 100 SOQL statements.

To debug this issue you should have a look in the log files. If you search for SOQL_EXECUTE_BEGIN you will find all the SOQL statements that have been called, there should be a repeating SOQL statement in there somewhere which is where the probably actually is.

| improve this answer | |
  • { string key = clr.LOB__c+'-'+clr.Company__c; if(keys.get(key)!=null) { keys.put(key,clr.Opt_Out__c); } } //Set Owner LOB Line 232 - map<Id, Contact> employeesByUser = UtilitiesContact.getEmployeeInfos(ownerids); – Dharmendra Jun 11 '15 at 6:02
  • Class.UtilitiesContact.getEmployeeInfos: line 96, column 1 global static map<Id, Contact> getEmployeeInfos(list<Id> userIds) { map<Id, Contact> employeeContacts = new map<Id, Contact>(); Line 96: for (Contact c : [SELECT Name, User__c, User__r.IsActive, LOB__c, Email, Phone,LOB__r.Short_Name__c, LOB__r.Name FROM Contact WHERE User__c IN :userIds]) { employeeContacts.put(c.User__c, c); } return employeeContacts; } – Dharmendra Jun 11 '15 at 6:03
  • If you have more information to add you your question then please click edit under your question and add the code there. – BarCotter Jun 11 '15 at 8:53
  • Thanks. I have update my question and provided complete details for your better understanding. – Dharmendra Jun 11 '15 at 10:08
  • It is hard to follow your code or see a possible issue. As stated in my answer: To debug this issue you should have a look in the log files. If you search for SOQL_EXECUTE_BEGIN you will find all the SOQL statements that have been called, there should be a repeating SOQL statement in there somewhere which is where the probably actually is. – BarCotter Jun 11 '15 at 10:19
1

This snippet of code is not hitting the governor limit by itself.

At a guess, I would say that you're calling getEmployeeInfos() in a loop.

Please post the rest of your code to confirm.

| improve this answer | |
0

Guess too late for answer but best guess here is your trigger Trigger.OpportunityBeforeUpdate_Core do not have recursion handling. Somewhere in your trigger you are updating opportunity which leads to fire this trigger again and again leading to 101 SOQL. Handle the recursion in your trigger.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.