Error: 'System.LimitException: Too many SOQL queries: 101' [duplicate]

Question

I am getting an error: 'System.LimitException: Too many SOQL queries: 101' when I run a testing class for another class similar to this one, which is located in the same Object. I am trying to use classes that handle all the code instead of writing the code in triggers. Can anybody please tell if the code i wrote for this class is good approach or should I use a different one.

I will appreciate any help!

Trigger code:

trigger RollupSalesAndUnits on MWSOrderItem__c (before update) {

//to fire the class rollupSalesTriger
if (Trigger.isUpdate) {

   RollupSalesAndUnits.Run(Trigger.new);
}
}

Class code:

public class RollupSalesAndUnits {


public static void Run(MWSOrderItem__c [] items){

 Map<Id, NRProducts__c > osos = new Map<Id, NRProducts__c >();


    for(MWSOrderItem__c p : items){

           if(!String.isEmpty(p.NRProduct__c )){

               NRProducts__c prod = osos.get(p.NRProduct__c );

            if (prod == null) {
                prod= new NRProducts__c (Id = p.NRProduct__c );

                osos.put(p.NRProduct__c , prod);
            }

            for(AggregateResult result : [SELECT  NRProduct__c p, SUM(Detail_Total__c) FROM MWSOrderItem__c WHERE NRProduct__c =: p.NRProduct__c AND AgeSet__c < 7 AND Order_Complete__c = 'Yes'  GROUP BY NRProduct__c]){

                 prod.X24_Hrs_Sales__c = (Double)result.get('expr0');               

            }

            for(AggregateResult result : [SELECT  NRProduct__c p, SUM(Detail_Total__c) FROM MWSOrderItem__c WHERE NRProduct__c =: p.NRProduct__c AND  AgeSet__c < 7  GROUP BY NRProduct__c]){


                 prod.X7_Days_Sales__c = (double)result.get('expr0');

             }

             for(AggregateResult result : [SELECT  NRProduct__c p, SUM(Detail_Total__c) FROM MWSOrderItem__c WHERE NRProduct__c =: p.NRProduct__c AND   AgeSet__c < 30  GROUP BY NRProduct__c]){


                 prod.X30_Day_Sales__c = (double)result.get('expr0');


             }

             for(AggregateResult result : [SELECT  NRProduct__c p, SUM(Detail_Total__c) FROM MWSOrderItem__c WHERE NRProduct__c =: p.NRProduct__c AND    AgeSet__c < 60  GROUP BY NRProduct__c]){


                  prod.X60_Days_Sales__c = (double)result.get('expr0');

             }




         }
     }


 update osos.values();  
 }

}

Answer 1

The problem with your approach is that you're running SOQL queries for every MWSOrderItem__c record, which is causing you to hit the limit of 100 SOQL queries any time that you have more than 25 records (each one is running 4 queries --> 100 SOQL queries). I think you're on the right track (especially with keeping your code in a separate class, instead of the trigger).

The biggest change you need to make is to pull back the aggregrate results for everything all at once instead of querying for each record separately. There are some other small changes I made (like spelling out variable names, instead of just using a single letter), but some of it is me just being nitpicky.

I've tried to update your class to show what I'm talking about - obviously I don't have your objects, etc, so I can't test that this works, so let me know if this gives you any errors.

    public class RollupSalesAndUnits {

public static void Run(List<MWSOrderItem__c> orderItems){

 Map<Id, NRProducts__c > osos = new Map<Id, NRProducts__c >();
    //Make a set that contains the product IDs of all products you want to calculate
    Set<Id> productIds = new Set<Id>();

    for(MWSOrderItem__c orderItem : orderItems){
        if(!String.isEmpty(orderItem.NRProduct__c )){
            productIds.add(NRProducts__c)
        }
        //Not sure what your intent is here - I've commented it out for now
        /*if (prod == null) {
            prod= new NRProducts__c (Id = orderItem.NRProduct__c );
            osos.put(orderItem.NRProduct__c , prod);
        }*/
    }

    //Create 4 maps: 1 for each of the aggregrates you want to calculate
    Map<Id, Integer> X24_Hrs_Sales_Map = new Map<Id, Integer>();
    Map<Id, Integer> X7_Days_Sales_Map = new Map<Id, Integer>();
    Map<Id, Integer> X30_Day_Sales_Map = new Map<Id, Integer>();
    Map<Id, Integer> X36_Day_Sales_Map = new Map<Id, Integer>();

    //Populate the maps with data for all NRProduct__c IDs, using the set we populated up above - this will only use 4 SOQL queries, regardless of how many records you have
    for(AggregateResult result : [SELECT  NRProduct__c p, SUM(Detail_Total__c) total FROM MWSOrderItem__c WHERE NRProduct__c IN :productIds AND AgeSet__c < 7 AND Order_Complete__c = 'Yes'  GROUP BY NRProduct__c]){               
          X24_Hrs_Sales_Map.put(String.valueOf(NRProduct__c),Double.valueOf(result.get('total'));  
    }

    for(AggregateResult result : [SELECT  NRProduct__c p, SUM(Detail_Total__c) total FROM MWSOrderItem__c WHERE NRProduct__c IN :productIds AND  AgeSet__c < 7 GROUP BY NRProduct__c]){
        X7_Days_Sales_Map = X7_Days_Sales_Map.put(String.valueOf(NRProduct__c),Double.valueOf(result.get('total'));  
    }

    for(AggregateResult result : [SELECT  NRProduct__c p, SUM(Detail_Total__c) total FROM MWSOrderItem__c WHERE NRProduct__c IN :productIds AND AgeSet__c < 30  GROUP BY NRProduct__c]){
        X30_Day_Sales_Map = X30_Day_Sales_Map.put(String.valueOf(NRProduct__c),Double.valueOf(result.get('total'));  
    }

    for(AggregateResult result : [SELECT  NRProduct__c p, SUM(Detail_Total__c) FROM MWSOrderItem__c WHERE NRProduct__c IN :productIds AND AgeSet__c < 60  GROUP BY NRProduct__c]){
        X36_Day_Sales_Map = X36_Day_Sales_Map.put(String.valueOf(NRProduct__c),Double.valueOf(result.get('total'));  
    }

    //One last query to get the products
    //We then use the maps to set the aggregrate results
    List<NRProduct__c> products = [SELECT Id FROM NRProduct__c WHERE Id IN :productIds];
    for(NRProduct__c product : products){
        p.X24_Hrs_Sales__c = X24_Hrs_Sales_Map.get(p.NRProduct__c);
        p.X7_Days_Sales__c = X7_Days_Sales_Map.get(p.NRProduct__c);
        p.X30_Day_Sales__c = X30_Day_Sales_Map.get(p.NRProduct__c);
        p.X60_Days_Sales__c = X36_Day_Sales_Map.get(p.NRProduct__c);
    }

    update products;
 }
}