2

I have been trying to build a wrapper class which creates a junction Object (Delivery_Note_Line_Items__c) between two other objects (orderLine & Delivery_Note__c) depending on the Visualforce's picked values.

I have adopted the standard Salesforce documentation (with some customisation) - Link

I am also using 4 declarative-lookup-rollup-summaries on the Junction Object, which update the Delivery_Note__c

My issue is the SOQL 101 limits. I have been debugging in the Dev console, using my test class to try and stress test - Inserting multiple records to see the behaviour of the extension, with and without the DLRS turned on (various combinations).

Question

If you are not supposed to query in a for loop, does this still apply to a Wrapper class? In the documentation, there is a query under the for loop?

I am not sure if my extension can be modified to avoid the governor limit?

UPDATE

The plot thickens. By debugging the test class for this, and turning off Process builders and dlrs triggers, I have found that there is a dlrs which keeps pushing the SOQL limits which although is hidden acts in the same way as the additional trigger below.

This trigger itself is pushing the limits from 3 queries, to 44! when the similar dlrs is set to active in its place, it's over 101...

Maybe i should go over to github and post there - DLRS

Any thoughts?

Extension:

public without sharing class DeliveryNoteSelectExtension {

    public String redirectUrl {get;set;}
    public decimal pickedValue {get;set;}

    public List<wrapOrderItem> wrapOrderItemList {get; set;}
    public List<OrderItem> selectedOrderItems {get;set;}

    public Delivery_Note__c currentRecord {get;set;}

    public DeliveryNoteSelectExtension(ApexPages.StandardController controller) {

        currentRecord = [SELECT id,name,order__c,Order__r.id FROM Delivery_Note__c WHERE id = :ApexPages.CurrentPage().getparameters().get('ID')];
        redirectUrl = '/'+currentRecord.Id;

        if(wrapOrderItemList == null) {
            wrapOrderItemList = new List<wrapOrderItem>();

            for(OrderItem o: [
                SELECT id,
                    Delivery_Note__c,Delivered__c,Delivery_Note__r.name,Delivery_Note__r.id,ListPrice,Product2.name,Product2.ProductCode,Quantity,
                    Quantity_Picked__c,Quantity_Back_Order__c,TotalPrice,Total_GST__c,Total_Including_GST__c
                FROM orderItem 
                WHERE orderid = :currentRecord.order__r.id 
                AND Quantity_Back_Order__c > 0
            ]){
                wrapOrderItemList.add(new wrapOrderItem(o));
            }
        }
    }

    public void processSelected() {
        selectedOrderItems = new List<orderItem>();

        List<Delivery_Note_Line_Item__c> delNoteLineItemsTobeInserted = new List<Delivery_Note_Line_Item__c>();

            for(wrapOrderItem wrapOrderItemObj : wrapOrderItemList) {
                if(wrapOrderItemObj.selected == true) {
                    selectedOrderItems.add(wrapOrderItemObj.oi);

                    Delivery_Note_Line_Item__c newDelItem = new Delivery_Note_Line_Item__c();
                    newDelItem.Order_Line_Item__c = wrapOrderItemObj.oi.Id;
                    newDelItem.Delivery_Note__c = ApexPages.currentPage().getParameters().get('id');
                    newDelItem.Quantity_Picked__c = wrapOrderItemObj.pickedValue;

                    delNoteLineItemsTobeInserted.add(newDelItem);
                }
            }
            if(delNoteLineItemsTobeInserted.size() > 0){
                insert delNoteLineItemsTobeInserted;
            }
        update selectedOrderItems;
        update delNoteLineItemsTobeInserted;
    }

    public class wrapOrderItem {
        public orderItem oi {get;set;}
        public Boolean selected {get;set;}
        public decimal pickedValue {get;set;}

        public wrapOrderItem(orderItem o) {
            oi = o;
            selected = false;
            pickedValue = 0;
        }
    }
}

Offensive Trigger

trigger DeliveryQuantities on Delivery_Note_Line_Item__c (After insert, After update, After delete, After undelete) {

    Set<id> orderItemIds = new Set<id>();
    Set<id> deliveryNoteIds = new Set<id>();

    If(Trigger.IsInsert || Trigger.IsUpdate || Trigger.IsUndelete){
        for (Delivery_Note_Line_Item__c dli : Trigger.new){
            orderItemIds.add(dli.Order_Line_Item__c);
            //deliveryNoteIds.add(dli.Delivery_Note__c);
        }
    }
    If(Trigger.IsDelete){
        For(Delivery_Note_Line_Item__c dli: Trigger.Old){
            orderItemIds.add(dli.Order_Line_Item__c);
            //deliveryNoteIds.add(dli.Delivery_Note__c);
        }
    }

    List<orderItem> OrderItemListToUpdate = New List<orderItem>();
    For(orderItem ordItem : [
        SELECT Id,Quantity_Picked__c,(
            SELECT id,Quantity_Picked__c FROM Delivery_Note_Line_Items__r) 
        FROM orderItem
        WHERE Id IN :orderItemIds]) {
            {
                ordItem.Quantity_Picked__c = 0;
                for(Delivery_Note_Line_Item__c dli: ordItem.Delivery_Note_Line_Items__r) {
                    ordItem.Quantity_Picked__c += dli.Quantity_Picked__c;
                }

                OrderItemListToUpdate.add(ordItem);                 
            }
            try{
                update OrderItemListToUpdate;
            }
            catch(Exception E){
                system.debug('Error thrown is: ' + E.getMessage());
            }
        }
    }
  • Where's the query in a for loop you speak of? I don't see that situation your code? – sfdcfox May 17 '18 at 5:32
  • My limited understanding - I thought you werent supposed to write a query under for...for(OrderItem o: [ SELECT id, Delivery_Note__c – TheMikeyBoosh May 17 '18 at 5:34
5

I guess you are confusing the standard practice with SOQL FOR Loop . SOQL For Loops are allowed, but standard practice is you do not write SOQL inside FOR loop. Like this:

For(Contact c: contacts){
   List<Account> accounts = [//SOME SOQL];
}

This type of coding is prohibited in Salesforce. The simple reason is you can easily cross the governor limits because of this. Once again "SOQL For Loop" is not "SOQL inside for loop".

  • Thanks Mr.Frodo. Do you know if the class posted can be modified to avoid the SOQL limit? I understand it's not the complete picture... – TheMikeyBoosh May 17 '18 at 6:19
  • 1
    Like sfdcfox has commented, i also don't see in the provided code that it could hit the governor limit. It looks fine to me. – Mr.Frodo May 17 '18 at 6:31
4

This is a good pattern to use as the query is executed once only and then the results are iterated over:

for (OrderItem o: [select ... from ... where ...]) {
    ...
}

This query inside a loop is the pattern to avoid as the query can be executed many times:

for (...) {
    OrderItem[] items = [select ... from ... where ...];
}

Readability is a problem with the first pattern but you can break the query onto multiple lines to help with that:

for (OrderItem o: [
        select ...
        from ...
        where ...
        ]) {
    ...
}

A benefit of the first pattern is that if there are many records they are automatically batched and so the amount of heap space required is automatically limited (to the batch of records not all the records).

See the SOQL For Loops documentation for more explanation.

  • Thanks Keith, a good explanation, which has helped me understand! – TheMikeyBoosh May 19 '18 at 1:26
0

Using the following debug line from the Salesforce Apex Developers guide, I managed to run the class in test mode until I found the issue.

System.debug('1.Number of Queries used in this apex code so far: ' + Limits.getQueries());

Although the debug log kept indicating that the DLRS trigger was the culprit, it was actually a process builder issue later on down the line.

I have a few questions about the order and SOQL limits on process builder / triggers and roll-ups when inserting several records, but i will ask that in another post!!

Thanks for your time all.

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.