System.LimitException: Too Many SOQL Queries: 101 in Trigger

Question

Following is my trigger I get the following error

System.LimitException: Too Many SOQL Queries: 101 in Trigger

Here is my trigger

trigger SubscriberUpdateTrigger on Subscriber__c (after Insert, after Update, after undelete) {

    Set<sfcloud__Product_Bundle__c> listBundleToUpdate = new Set<sfcloud__Product_Bundle__c>();
    sfcloud__Product_Bundle__c pb = new sfcloud__Product_Bundle__c();        

    List<Subscriber__c> subsList = [select id,Subscriber__c.Product_Bundle__c,Active_Subscriber__c,Subscriber_Price_Book__r.id,Subscriber_Price_Book__c,Subscriber_Contact__r.Id,Subscriber_Contact__r.Is_Subscriber__c from Subscriber__c where id IN :Trigger.newMap.keySet()];

    for(Subscriber__c ss : subsList)    
    {        
            if(ss.Product_Bundle__c != null){

                sfcloud__Product_Bundle__c spb = [Select id,Vision__c,Telemedicine__c,Dental__c, Fulfillment__c, name, sfcloud__Price_Book__c,sfcloud__Price_Book__r.id from sfcloud__Product_Bundle__c where id =: ss.Product_Bundle__c];
                System.debug('Total Bundle :'+spb);                
                List<PricebookEntry> pbe = [Select Id, Name, ProductCode, Product2Id, UnitPrice From PricebookEntry where Pricebook2Id =: spb.sfcloud__Price_Book__c];
                System.debug('Total PriceEntry:'+pbe.size());                

                 for(PricebookEntry pbe1 : pbe){    

                    if(pbe1.ProductCode == 'Fulfillment'){
                        pb.Id = spb.Id;
                        pb.Fulfillment__c = True;
                    }
                    if(pbe1.ProductCode == 'Discount_Vision'){
                        pb.Id = spb.Id;
                        pb.Vision__c = True;
                    }
                    if(pbe1.ProductCode == 'Discount_Dental'){
                        pb.Id = spb.Id;
                        pb.Dental__c = True;
                    }
                    if(pbe1.ProductCode == 'Telemed_Standard'){    
                        pb.Id = spb.Id;
                        pb.Telemedicine__c = True;
                    } 
                }
                listBundleToUpdate.add(pb);

            }
            System.debug('List'+listBundleToUpdate);

        if(!listBundleToUpdate.isEmpty()){
            List<sfcloud__Product_Bundle__c> subs = new List<sfcloud__Product_Bundle__c>(listBundleToUpdate);
            UPDATE subs;
        }
    }
}

Thanks, Vimal

Answer 1

Here is how to move the two queries out of the loop.

This is a common pattern, where you first loop over the trigger objects to collect the set of related object IDs and then do a set oriented query on those into maps. Then within the main processing loop you can lookup those results from the maps rather than querying for individual items.

You should also take care to do the final update outside of the loop.

trigger SubscriberUpdateTrigger on Subscriber__c (after Insert, after Update, after undelete) {

    Set<Id> productBundleIds = new Set<Id>();
    for (Subscriber__c ss : Trigger.new) {
        if (ss.Product_Bundle__c != null) {
            productBundleIds.add(ss.Product_Bundle__c);
        }
    }
    Map<Id, sfcloud__Product_Bundle__c> productBundles = new Map<Id, sfcloud__Product_Bundle__c>([
            Select id,Vision__c,Telemedicine__c,Dental__c, Fulfillment__c, name, sfcloud__Price_Book__c,sfcloud__Price_Book__r.id
            from sfcloud__Product_Bundle__c
            where Id in :productBundleIds
            ]);

    Set<Id> priceBookIds = new Set<Id>();
    for (sfcloud__Product_Bundle__c spb : productBundles.values()) {
        if (spb.Product_Bundle__c != null) {
            productBundleIds.add(spb.sfcloud__Price_Book__c);
        }
    }
    Map<Id, List<PricebookEntry>> priceBooks = new Map<Id, List<PricebookEntry>>();
    for (PricebookEntry pbe : [
            Select Id, Name, ProductCode, Product2Id, UnitPrice, Pricebook2Id
            From PricebookEntry
            where Pricebook2Id in :priceBookIds
            ]) {
        List<PricebookEntry> l = priceBooks.get(pbe.Pricebook2Id);
        if (l == null) {
            l = new List<PricebookEntry>();
            priceBooks.put(pbe.Pricebook2Id, l);
        }
        l.add(pbe);
    }

    for (Subscriber__c ss : Trigger.new) {        
        if (ss.Product_Bundle__c != null) {
            sfcloud__Product_Bundle__c spb = productBundles.get(ss.Product_Bundle__c);             
            List<PricebookEntry> pbe = priceBooks.get(spb.sfcloud__Price_Book__c);

            // Rest of loop logic

        }
    }

    // Do one update here
}

Answer 2

You are doing query in for loop. That is why you are getting this error. You need to bulkify your code using List, Map and Set.

For more details check following link which contains Apex Code Best Practices and Bulkify your Code

https://developer.salesforce.com/page/Apex_Code_Best_Practices

Answer 3

You need to query your Bundle and Price-books outside of the loop and hold them in a collection. I would recommend a map.

Here is how you would do it for your bundle object

map<Id,sfcloud__Product_Bundle__c> bundleMap = new map<Id,sfcloud__Product_Bundle__c>();
for(Subscriber__c sub : trigger.new){
     bundleMap.put(sub.Product_Bundle__c, null);
}
bundleMap.remove(null);
bundleMap.putAll([Select Id, Name,Vision__c,Telemedicine__c,Dental__c, Fulfillment__c, sfcloud__Price_Book__c,sfcloud__Price_Book__r.id from sfcloud__Product_Bundle__c where Id In : bundleMap.keyset()];

Then within your loop, you now have access to Bundle object related to a particular Subscriber and can access it like this

for(Subscriber__c sub : trigger.new)  {        
       if(ss.Product_Bundle__c == null){
            continue;
       }
       sfcloud__Product_Bundle__c spb = bundleMap.get(sub.Product_Bundle__c);
}

You need to use the same concept to get the Pricebook Entries as well. Utilize a collection to gather the data out side of your loop, and then reference that collection to get the pertinent record inside of your loop. This saves your queries and will help avoid governor limits.

And as everyone else suggested, I would really try to study the Apex coding best practices. you can find this technique along with many others

https://developer.salesforce.com/page/Apex_Code_Best_Practices