+ Start a Discussion
avimeiravimeir 

Getting System.LimitException: Too many SOQL queries: 101, how to solve?

Hi all,

 

I am getting the error: 

 

Error:Apex trigger handleReservationChange caused an unexpected exception, contact your administrator: handleReservationChange: System.LimitException: Too many SOQL queries: 101

 

I know that it's wrong to have the SOQL queries inside the for loops, but can't understand how to perform what I need otherwise. Could someone please post a code sample of how I could take the SOQL queries outside of the loops?

 

 

private static void assignPricesToReservation (ReservationRoomAssociation__c assoc)
    {
    	Double todayRate = 0;
		Date currentDay;
		
		System.debug('adding assoc ' + assoc);
		
		// Initialize price
		assoc.Price__c = 0;
		
          
        // Get room type                            
		Room_Type__c rt = [SELECT Name,Number_of_Rooms__c, Default_Rate__c, Id
		 				   FROM Room_Type__c 
		 				   WHERE Id = :assoc.Room_Type__c LIMIT 1];
		System.debug('found roomtype ' + rt);		 
		String roomType = rt.Name;
		System.debug('Room type is ' + roomType);
		
		// Get reservation
		Reservation__c resa = [SELECT Total_Nights__c, Check_In__c, Check_Out__c, Total_Price__c
		 					   FROM Reservation__c 
		 					   WHERE Id = :assoc.Rooms__c LIMIT 1];
	        
        // Add price per night    
		for (Integer i = 0; i < resa.Total_Nights__c; i++)
        {
            currentDay = resa.Check_In__c.addDays(i);          
            System.debug('Date: ' + currentDay);
           
            // Check for availability for the night
            try 
            {      
                Availability__c avail = [SELECT Name, Rooms_Available__c,Room_Type__c 
                                         FROM Availability__c 
                                         WHERE Date__c = :currentDay AND Room_Type__r.Name = :roomType];

                // Yes, there is availability
                if (avail.Rooms_Available__c <= 0)
                {
                	throw new reservationException('No availablity for the selected dates');
                }		
                else
                {
                	// Remove one room from the total available rooms of this date / room-type match
                	avail.Rooms_Available__c -= 1;
                	update avail;
                }	    
		    }
		    catch (Exception e) 
			{
				// No availability record, create one
			    System.debug('no availability record, creating a new one with max rooms ' + rt.Number_of_Rooms__c);
			    Availability__c avail = new Availability__c ();
			    avail.Date__c = currentDay;
			    avail.Rooms_Available__c = rt.Number_of_Rooms__c - 1;
			    avail.Total_Rooms__c = rt.Number_of_Rooms__c;
			    avail.Room_Type__c = rt.Id;
			    insert avail;
			    	    			    
			}
			
			
			try 
            {
			    // Is there a specific rate for this day?
			    Date_Rate__c dateRate = [SELECT Price__c
			    						 FROM Date_Rate__c
			    						 WHERE Date__c = :currentDay
			    						 AND Room_Type__r.Name = :roomType
			    						 ];
				 if (dateRate != null)
				 {
				 	todayRate = dateRate.Price__c;
					System.debug('got date-specific rate for ' + currentDay + ' rt: ' + roomType + ' rate is: ' + todayRate);			 
				 }
			}
			catch (Exception e)
			{
			 	todayRate = rt.Default_Rate__c;    
				System.debug('couldn\'t find specific rate for ' + currentDay + ' using default rate: ' + todayRate);	 	
			}
			
	        // Add the rate for this date and room type   
	        System.debug('adding rate to the total price: ' + todayRate);   
	        assoc.Price__c += todayRate;
	          		
	        if (resa.Total_Price__c != null)
	        {
	        	resa.Total_Price__c += todayRate; 
	        }
	        else
	        {
	        	resa.Total_Price__c = todayRate;
	        } 
		            
        }
	    System.debug('New reservation total price is ' + resa.Total_Price__c);   
	    update resa;   	
    }
    
	private static void calculateReservationPrice (Reservation__c r)
	{
		try 
		{
			Reservation__c resa = [SELECT Total_Price__c FROM Reservation__c WHERE Id = :r.Id];
			resa.Total_Price__c = 0;
			update resa;
		}
		catch (Exception e)
		{
			System.debug('couldn\'t set reservation price to 0');
		}
		
		System.debug('calculating new reservation price');
				
		// Get all room associations for this resa
		List <ReservationRoomAssociation__c> assoc = [SELECT a.Id, a.Room_Type__c, a.Rooms__c
												      FROM ReservationRoomAssociation__c a
												      WHERE a.Rooms__c = :r.Id];
		
		System.debug('updating pricing for ' + r.Name);
		
		for (ReservationRoomAssociation__c a : assoc)
		{
			System.debug('Assigning price for association ' + a);
			assignPricesToReservation(a);
		}
	}
	public static void handleReservationChange(List <Reservation__c> oldResas, 
											   List <Reservation__c> newResas)
    {
    	System.debug('handleReservationChange');
    	
    	
    	Reservation__c oldResa;
    	// Check if reservation changed in length
    	for (Reservation__c r : newResas)
    	{
    		oldResa = null;
    		
    		// get the corresponding old resa
    		System.debug('in reservation iteration loop');
 
    		for (Reservation__c oldR : oldResas)
    		{
    			if (oldR.Id == r.Id)
    			{
    				oldResa = oldR;
    				break;
    			}
    		}
    		if (oldResa == null)
    		{
    			throw new reservationException('can\'t find old reservations');
    		}
    		
    		System.debug('old nights: ' + oldResa.Total_Nights__c + ' new nights: ' + r.Total_Nights__c);
    		
    		if (oldResa.Total_Nights__c != r.Total_Nights__c)
    		{
    			System.debug('calling caluclateReservationPrice for' + r);
    			calculateReservationPrice(r);
    		}
    	}
    	
    	
    }
    

 

Thanks!

 

Best Answer chosen by Admin (Salesforce Developers) 
Alex.AcostaAlex.Acosta

Here's an example of how to do your first 2 methods that you call from your trigger... This will save both time in the way records are bring process and number of script lines executed. It's also more optimized to save you a few queries already if you compare your prior methods

 

public static void handleReservationChange(Map<Id, Reservation__c> oldResas, List <Reservation__c> newResas){
	Reservation__c oldResa;
	List<Reservation__c> recalculateReservationsCollection = new List<Reservation__c>();
	
	// Check if reservation changed in length
	for (Reservation__c r : newResas){
		oldResa = null;
		
		// get the corresponding old resa
		System.debug('in reservation iteration loop');

		if (oldResas.containsKey(r.Id)){
			oldResa = oldResas.get(r.Id);
		}else{
			throw new reservationException('can\'t find old reservations');
		}
		
		System.debug('old nights: ' + oldResa.Total_Nights__c + ' new nights: ' + r.Total_Nights__c);
		
		if (oldResa.Total_Nights__c != r.Total_Nights__c)
		{
			System.debug('calling caluclateReservationPrice for' + r);
			recalculateReservationsCollection.add(r);
		}
	}
	
	if(null != recalculateReservationsCollection && recalculateReservationsCollection.size() > 0){
		calculateReservationPrice(recalculateReservationsCollection);
	}
}

 

private static void calculateReservationPrice (List<Reservation__c> r){
	Set<Id> reservationIds = new Set<Id>();
	for(Reservation__c res :r){
		reservationIds.add(r.Id);
	}
	
	List<Reservation__c> updateReservationList = new List<Reservation__c>();
	for(Reservation__c res :[SELECT Id, Total_Price__c FROM Reservation__c WHERE Id IN :reservationIds]){	
		resa.Total_Price__c = 0;
		updateReservationList.add(resa);
	}
	if(null != updateReservationList && updateReservationList.size() > 0){
		update updateReservationList;
	}
	
	System.debug('calculating new reservation price');
			
	// Get all room associations for this resa
	List <ReservationRoomAssociation__c> assoc = [SELECT a.Id, a.Room_Type__c, a.Rooms__c FROM ReservationRoomAssociation__c a WHERE a.Rooms__c IN :reservationIds];
	
	System.debug('updating pricing for ' + r.Name);
	
	for (ReservationRoomAssociation__c a : assoc)
	{
		System.debug('Assigning price for association ' + a);
		//assignPricesToReservation(a); TODO: figure out how to pass information to last method
	}
}

 

All Answers

Alex.AcostaAlex.Acosta

Here's an example of how to do your first 2 methods that you call from your trigger... This will save both time in the way records are bring process and number of script lines executed. It's also more optimized to save you a few queries already if you compare your prior methods

 

public static void handleReservationChange(Map<Id, Reservation__c> oldResas, List <Reservation__c> newResas){
	Reservation__c oldResa;
	List<Reservation__c> recalculateReservationsCollection = new List<Reservation__c>();
	
	// Check if reservation changed in length
	for (Reservation__c r : newResas){
		oldResa = null;
		
		// get the corresponding old resa
		System.debug('in reservation iteration loop');

		if (oldResas.containsKey(r.Id)){
			oldResa = oldResas.get(r.Id);
		}else{
			throw new reservationException('can\'t find old reservations');
		}
		
		System.debug('old nights: ' + oldResa.Total_Nights__c + ' new nights: ' + r.Total_Nights__c);
		
		if (oldResa.Total_Nights__c != r.Total_Nights__c)
		{
			System.debug('calling caluclateReservationPrice for' + r);
			recalculateReservationsCollection.add(r);
		}
	}
	
	if(null != recalculateReservationsCollection && recalculateReservationsCollection.size() > 0){
		calculateReservationPrice(recalculateReservationsCollection);
	}
}

 

private static void calculateReservationPrice (List<Reservation__c> r){
	Set<Id> reservationIds = new Set<Id>();
	for(Reservation__c res :r){
		reservationIds.add(r.Id);
	}
	
	List<Reservation__c> updateReservationList = new List<Reservation__c>();
	for(Reservation__c res :[SELECT Id, Total_Price__c FROM Reservation__c WHERE Id IN :reservationIds]){	
		resa.Total_Price__c = 0;
		updateReservationList.add(resa);
	}
	if(null != updateReservationList && updateReservationList.size() > 0){
		update updateReservationList;
	}
	
	System.debug('calculating new reservation price');
			
	// Get all room associations for this resa
	List <ReservationRoomAssociation__c> assoc = [SELECT a.Id, a.Room_Type__c, a.Rooms__c FROM ReservationRoomAssociation__c a WHERE a.Rooms__c IN :reservationIds];
	
	System.debug('updating pricing for ' + r.Name);
	
	for (ReservationRoomAssociation__c a : assoc)
	{
		System.debug('Assigning price for association ' + a);
		//assignPricesToReservation(a); TODO: figure out how to pass information to last method
	}
}

 

This was selected as the best answer
avimeiravimeir

Thanks Alex, very helpful!!