+ Start a Discussion
Dan NorfleetDan Norfleet 

Apex List / Map Question

I am new to Apex Coding and have a hurdle people with more experience may be able to help me overcome.  When an update to an order is triggered, it is performing an Apex Class that does sharing and some other things.  I am attempting to add a method to this Class that will identify duplicate orders item combinations and delete them from the order.

In the following code I am able to display that the "AggregateResult[] ccOrders" query is returning the correct information.  I would like to take those variables and load them into a list where I can delete the records.  I am getting "Error: Compile Error: Invalid bind expression type of APEX_OBJECT for column of type Id at line ......" when i try to compile the class with "List<ccrz__E_OrderItem__c> OrdItemDel" query.  I have displayed the values that are used in the WHERE clause and those values are correct.

Question 1:  Is this the best way to delete those extra Order Item records that are duplicates (I have identified them successfully now want to delete them).
Question 2:  Can someone suggest what would fix the compile issue.
Question 3:  Once the query works and loads the rows to the list, i was planning to use the delete statement with exception processing around it, if you have suggestion on that, it is much appreciated.

The current code for the method in question is listed below.

Dan


/******************************************************************************
 * Determine if any of the orders have duplicate Items.
 * @param List<Id> orderIds 
 */
    public static void checkDuplicateOrderItems( List<Id> orderIds )
    {
        if( orderIds.isEmpty() ) return;

        // Identify Orders within the list that have duplicate items
                AggregateResult[] ccOrders =
             [SELECT ccrz__order__c ord_id
                              ,ccrz__product__r.ccrz__sku__c ord_sku
                              ,ccrz__orderlinetype__c ord_sku_type
                              ,ccrz__category__r.ccrz__categoryid__c ord_sku_catid
                              ,ccrz__category__r.name ord_sku_catname
                              ,COUNT(Id) dupitemcnt
                        FROM ccrz__E_OrderItem__c 
                        WHERE ccrz__Order__r.Id IN :orderIds 
                        GROUP BY ccrz__order__c
                                    ,ccrz__product__r.ccrz__sku__c
                                    ,ccrz__orderlinetype__c
                                    ,ccrz__category__r.ccrz__categoryid__c
                                    ,ccrz__category__r.name
                        HAVING Count(Id) > 1];

                 // Exit Method loop if there are no duplicates in the orders being processed
        if( ccOrders.isEmpty() ) return;
        
                for (AggregateResult ar : ccOrders)  {
                    Object v_ord_id = ar.get('ord_id');
                    Object v_ord_sku = ar.get('ord_sku');
                    Object v_ord_sku_type = ar.get('ord_sku_type');
                    Object v_ord_sku_catid = ar.get('ord_sku_catid');
                    Object v_ord_sku_catname = ar.get('ord_sku_catname');
                    Integer v_dupitemcnt = (Integer)ar.get('dupitemcnt');
                    System.debug('-------- DUPLICATE ORDER SKU -----------');
                    System.debug('ord_id = ' + v_ord_id);
                    System.debug('ord_sku   = ' + v_ord_sku);
                    System.debug('ord_sku_type = ' + v_ord_sku_type);
                    System.debug('ord_sku_catid = ' + v_ord_sku_catid);
                    System.debug('ord_sku_catname = ' + v_ord_sku_catname);
                    System.debug('dupitemcnt = ' + v_dupitemcnt);
                    
                    List<ccrz__E_OrderItem__c> OrdItemDel =
                        [SELECT ID 
                                   ,ccrz__product__r.ccrz__sku__c
                                   ,ccrz__orderlinetype__c
                                   ,ccrz__category__r.ccrz__categoryid__c
                                   ,ccrz__category__r.name
                                   ,ccrz__quantity__c
                         FROM ccrz__E_OrderItem__c
                         WHERE ccrz__order__c = :v_ord_id
                             AND ccrz__product__r.ccrz__sku__c = :v_ord_sku
                             AND ccrz__orderlinetype__c = :v_ord_sku_type
                             AND ccrz__category__r.ccrz__categoryid__c = :v_ord_sku_catid
                         ORDER BY ccrz__quantity__c DESC
                         LIMIT :v_dupitemcnt - 1];
                }
    }
}
Best Answer chosen by Dan Norfleet
Steven NsubugaSteven Nsubuga
I would need more time to get into your code. It seems you loop through the AggregateResults, and for each AggregateResult, you query for a single ccrz__E_OrderItem__c.
The ideal scenario is to loop through the AggregateResults and for each AggregateResult, you store in a Set the relevant variables to be used in 1 SOQL query to retrieve all the necessary ccrz__E_OrderItem__c records. This means that no matter how many AggregateResult records you have, you would only have a single query to retrieve the ccrz__E_OrderItem__c.
Right now, if you have more than 100 records returned by AggregateResult[] ccOrders, this code as it is will fail.

All Answers

Steven NsubugaSteven Nsubuga
Perhaps you should explicitly cast the variables into their type, for example String, Id etc rather than Object, and then use the typed variables in your where clause.
Steven NsubugaSteven Nsubuga
And another thing, never include a SOQL query inside of a loop. You will hit the too many soql queries limit. 
Dan NorfleetDan Norfleet
Steven, thanks for the feedback.  Casting the variables was the answer!  Regarding the second post on SOQL in a FOR loop - The for loop identifies the items that are duplicates on the order then the second set takes each of those rows and reads each of the duplicate records into a list.  Then I delete after the FOR loop.  The logic appears to work, but please advice if and how the code should be changed.  I thought I would need the SELECT within the FOR loop since the variable values depend on those returned from each row.  Following is my current code.

Dan

/******************************************************************************
 * Determine if any of the orders have duplicate Items.
 * @param List<Id> orderIds 
 */
    public static void checkDuplicateOrderItems( List<Id> orderIds )
    {
        System.debug( 'START: TriggerCC_HDPC_Order.checkDuplicateOrderItems()' );
        System.debug( 'Param: orderIds = ' + orderIds );
        
        if( orderIds.isEmpty() ) return;

        // Identify Orders within the list that have duplicate items
        AggregateResult[] ccOrders =
             [SELECT ccrz__order__c ord_id
                     ,ccrz__product__r.ccrz__sku__c ord_sku
                     ,ccrz__orderlinetype__c ord_sku_type
                     ,ccrz__category__r.ccrz__categoryid__c ord_sku_catid
                     ,ccrz__category__r.name ord_sku_catname
                     ,COUNT(Id) dupitemcnt
              FROM ccrz__E_OrderItem__c 
              WHERE ccrz__Order__r.Id IN :orderIds 
              GROUP BY ccrz__order__c
                          ,ccrz__product__r.ccrz__sku__c
                          ,ccrz__orderlinetype__c
                          ,ccrz__category__r.ccrz__categoryid__c
                          ,ccrz__category__r.name
              HAVING Count(Id) > 1];

        // Exit Method loop if there are no duplicates in the orders being processed
        if( ccOrders.isEmpty() ) return;

        List<ccrz__E_OrderItem__c> OrdItemDel = new List<ccrz__E_OrderItem__c>();
        ccrz__E_OrderItem__c OrdItemDelTemp = new ccrz__E_OrderItem__c();
        
        for (AggregateResult ar : ccOrders)  {
             System.debug('---------- DUPLICATE ORDER SKU -------------');
             System.debug('     ord_id          = ' + ar.get('ord_id'));
             System.debug('     ord_sku         = ' + ar.get('ord_sku'));
             System.debug('     ord_sku_type    = ' + ar.get('ord_sku_type'));
             System.debug('     ord_sku_catid   = ' + ar.get('ord_sku_catid'));
             System.debug('     ord_sku_catname = ' + ar.get('ord_sku_catname'));
             System.debug('     dupitemcnt      = ' + ar.get('dupitemcnt'));

          
             OrdItemDelTemp =
                [SELECT id,
                          ccrz__product__r.ccrz__sku__c,
                          ccrz__orderlinetype__c,
                          ccrz__category__r.ccrz__categoryid__c,
                          ccrz__category__r.name,
                          ccrz__quantity__c
                 FROM ccrz__E_OrderItem__c
                 WHERE ccrz__order__c = :((ID)ar.get('ord_id'))
                   AND ccrz__product__r.ccrz__sku__c = :((String)ar.get('ord_sku'))
                             AND ccrz__orderlinetype__c = :((String)ar.get('ord_sku_type'))
                             AND ccrz__category__r.ccrz__categoryid__c = :((String)ar.get('ord_sku_catid'))
                         ORDER BY ccrz__quantity__c
                         LIMIT :((Integer)ar.get('dupitemcnt')) - 1];
                         
                         OrdItemDel.add(OrdItemDelTemp);
        }
        System.debug('**** DUPLICATE ORDER ITEMS TO DELETE:  '+ OrdItemDel);
        try 
            {
                               delete OrdItemDel;
            } 
            catch (Exception e) 
            {
                           System.debug('cc_HDPC_TriggerOrder.checkDuplicateOrderItems - ERROR DELETING DUPLICATE ORDER ITEMS - MSG = ' + e.getMessage().substring(1,255));
            }

        System.debug( 'EXIT: TriggerCC_HDPC_Order.checkDuplicateOrderItems()' );
    }
 
Steven NsubugaSteven Nsubuga
I would need more time to get into your code. It seems you loop through the AggregateResults, and for each AggregateResult, you query for a single ccrz__E_OrderItem__c.
The ideal scenario is to loop through the AggregateResults and for each AggregateResult, you store in a Set the relevant variables to be used in 1 SOQL query to retrieve all the necessary ccrz__E_OrderItem__c records. This means that no matter how many AggregateResult records you have, you would only have a single query to retrieve the ccrz__E_OrderItem__c.
Right now, if you have more than 100 records returned by AggregateResult[] ccOrders, this code as it is will fail.
This was selected as the best answer