function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nikki Phillips 8Nikki Phillips 8 

Rollup creating too many SOQL Queries

I need to create a rollup of prices that is coming from an object called Events (This is usally in multiples). These Events have three different objects associated with each one and these three objects can have many records under each. I realized that if I have more than just a few events I get an SOQL query limit.  I am quite new to salesforce and most of my team is off preparing them for a release so I do not want to bug them.  I'm not too sure the code so if you could help me more than just saying the technical way of doing things and put it into simpler terms a noob would understand I would be forever in your debt.
  
public with sharing class BookingInclusivePriceInlineExt {

	public Booking__c booking { get; set;}

    private QueryTaxGroupSchedule qtgs;
	private BookingEvent__c bEvent;
	public BookingInclusivePriceInlineExt(ApexPages.standardController stdController) 
	{
		booking = new Booking__c();

        booking.AtDefiniteBlendedEventRevenue1__c = 0;
        booking.AtDefiniteBlendedEventRevenue2__c = 0;
        booking.AtDefiniteBlendedEventRevenue3__c = 0;
        booking.AtDefiniteBlendedEventRevenue4__c = 0;
        booking.AtDefiniteBlendedEventRevenue5__c = 0;
        booking.AtDefiniteBlendedEventRevenue6__c = 0;
        booking.AtDefiniteBlendedEventRevenue7__c = 0;
        booking.AtDefiniteBlendedEventRevenue8__c = 0;
        booking.AtDefiniteBlendedEventRevenue9__c = 0;
        booking.AtDefiniteBlendedEventRevenue10__c = 0;
        booking.AtDefiniteBlendedEventRevenue11__c = 0;
        booking.AtDefiniteBlendedEventRevenue12__c = 0;
        booking.AtDefiniteBlendedEventRevenue13__c = 0;
        booking.AtDefiniteBlendedEventRevenue14__c = 0;
        booking.AtDefiniteBlendedEventRevenue15__c = 0;  
         		
    	//Booking__c bookingRecord = new BookingRecord__c();	
    	Booking__c bookingRecord = (Booking__c) stdController.getRecord();
    	
    	for(BookingEvent__c bEvent : [Select Id, TaxGroup__c from BookingEvent__c Where Booking__c =: bookingRecord.id])
    	{
    		BookingEventInclusiveRollup(bEvent.Id, bEvent.TaxGroup__c);
    	}
    	
    	for(BookingOtherIncome__c boi : [Select Id, TaxGroup__c, AdminIsIncludedInInclusivePrice__c, GratuityIsIncludedInInclusivePrice__c, Gratuity__c, AdminCharge__c, RevenueClassification__c, RevenueClassification__r.CategoryMapping__c From BookingOtherIncome__c where Booking__c =: bookingRecord])
    	{
    		
    		InclusiveRevenueBreakdown(boi.RevenueClassification__c, boi.GratuityIsIncludedInInclusivePrice__c, boi.AdminIsIncludedInInclusivePrice__c, boi.UnitPrice__c, boi.AdminCharge__c, boi.Gratuity__c, boi.RevenueClassification__r.CategoryMapping__c, boi.BookingPackageEvent__r.PackageCovers__c, boi.BookingPackageEvent__r.BookingEvent__r.TaxGroup__c);
    	}	
	}
	
    private void BookingEventInclusiveRollup(id bEvent, id taxGroup)
   		{
   		qtgs = new QueryTaxGroupSchedule(taxGroup, false);
   		 		
   		for(EventPackageRevenueBreakdown__c eprb : [Select Id, BookingPackageEvent__r.PackageCovers__c, AdminIsIncludedInInclusivePrice__c, GratuityIsIncludedInInclusivePrice__c, Gratuity__c, AdminCharge__c, UnitPrice__c, RevenueClassification__c, RevenueClassification__r.CategoryMapping__c, BookingPackageEvent__r.BookingEvent__r.TaxGroup__c from EventPackageRevenueBreakdown__c where BookingPackageEvent__r.BookingEvent__c =: bEvent])
   		{	
        	InclusiveRevenueBreakdown(eprb.RevenueClassification__c, eprb.GratuityIsIncludedInInclusivePrice__c, eprb.AdminIsIncludedInInclusivePrice__c, eprb.UnitPrice__c, eprb.AdminCharge__c, eprb.Gratuity__c, eprb.RevenueClassification__r.CategoryMapping__c, eprb.BookingPackageEvent__r.PackageCovers__c, eprb.BookingPackageEvent__r.BookingEvent__r.TaxGroup__c);
       	}
     
       	for(EventItem__c eventItems : [Select Id,  ActualQuantity__c, UnitPrice__c, AdminCharge__c, AdminIsIncludedInInclusivePrice__c, GratuityIsIncludedInInclusivePrice__c, Gratuity__c, RevenueClassification__c, RevenueClassification__r.CategoryMapping__c, Event__r.TaxGroup__c from EventItem__c where EventItem__c.ItemType__c =: 'Item' and Event__c =: bEvent and PriceWithMenu__c = false])
       	{
       		InclusiveRevenueBreakdown(eventItems.RevenueClassification__c, eventItems.GratuityIsIncludedInInclusivePrice__c, eventItems.AdminIsIncludedInInclusivePrice__c, eventItems.UnitPrice__c, eventItems.AdminCharge__c, eventItems.Gratuity__c, eventItems.RevenueClassification__r.CategoryMapping__c, eventItems.ActualQuantity__c, eventItems.Event__r.TaxGroup__c);
       	}
        	
       	for(EventItemRevenueBreakdown__c itemRev : [Select Id, EventItem__r.ActualQuantity__c, AdminIsIncludedInInclusivePrice__c, GratuityIsIncludedInInclusivePrice__c, Gratuity__c, AdminCharge__c, UnitPrice__c, RevenueClassification__r.CategoryMapping__c, RevenueClassification__c, EventItem__r.Event__r.TaxGroup__c from EventItemRevenueBreakdown__c where  EventItem__r.Event__c =: bEvent])
		{
			InclusiveRevenueBreakdown(itemRev.RevenueClassification__c, itemRev.GratuityIsIncludedInInclusivePrice__c, itemRev.AdminIsIncludedInInclusivePrice__c, itemRev.UnitPrice__c, itemRev.AdminCharge__c, itemRev.Gratuity__c, itemRev.RevenueClassification__r.CategoryMapping__c, itemRev.EventItem__r.ActualQuantity__c, itemRev.EventItem__r.Event__r.TaxGroup__c);
       	}
    }
      
    private void InclusiveRevenueBreakdown(id revClassification, boolean gratIncluded, boolean adminIncluded, decimal unitPrice, decimal adminCharge, decimal gratuity, decimal revCategory, decimal quantity, id taxGroup){
    	
    	qtgs = new QueryTaxGroupSchedule(taxGroup, false);
    	decimal totalRate = qtgs.GetInclusiveBaseRate(taxGroup, revClassification);
        
        if(adminIncluded)
            totalRate += (adminCharge==null?0: adminCharge / 100)*(qtgs.GetInclusiveAdminRate(taxGroup, revClassification));
        if(gratIncluded)
            totalRate += (gratuity==null?0: gratuity / 100)*(qtgs.GetInclusiveGratuityRate(taxGroup, revClassification));
        
        booking.put('AtDefiniteBlendedEventRevenue' + String.valueOf(revCategory) + '__c', ((decimal) booking.get('AtDefiniteBlendedEventRevenue' + String.valueOf(revCategory) + '__c') + (unitPrice==null?0:unitPrice)*(totalRate) * (quantity == null? 1:quantity)));              	
    }
}

 
James LoghryJames Loghry
Salesforce is a multi-tenant architecture and needs to throttle requests to make sure that no one application can impact others on its system.  This is why there are governor limits put in place.  The particular one you're running into is a SOQL query limit (which is a limit of 100 queries per transaction).  The way around this is to "bulkify" your code, or issue as little SOQL queries as possible.  

You can do this by utilizing parent / child relationships as well as maps.  The current state of your code however, is that you're looping through records, and then issuing additional queries for these records. So instead of 1 query for every 200 records, you're actually looking at  200 records * 1 query per record or 200 queries, which is a lot.

I suggest taking a quick read through the following blog post: Alternatively to your method, I'd suggest at either implementing this in a batch process or a series of batch processes, or utilizing the excellent Declaritive Rollup Summary tool that can be found here: https://developer.salesforce.com/page/Declarative_Rollup_Summary_Tool_for_Force.com_Lookup_Relationships
david roggerdavid rogger
This rollup is deploying a new trigger. When trying to deploy the trigger I get an error on the Contact class. When you ask about the contact information do you mean on all the rollups I have for the contact record? This trigger that I am trying to deploy is not on the contact record. I do not have any rollups that the contact is the parent and child.