Below is some code I whipped up, you will have to check it for typos and such but it shows the flow I alluded to in my first comment.
list<AggregateResult> badorders = [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];
Set<ID> ord_ids = new Set<ID>();
Set<string> ord_skus = new Set<string>();
Set<string> ord_sku_types = new Set<string>();
Set<string> ord_sku_catids = new Set<string>();
//loop over every row we found, and add the dupe identifiers to sets so we can find them again in another query.
for(AggregateResult ar : badorders) {
ord_ids.add((ID)ar.get('ord_id'));
ord_skus.add(string.valueOf(ar.get('ord_sku')));
ord_sku_types.add(string.valueOf(ar.get('ord_sku_type')));
ord_sku_catids.add(string.valueOf(ar.get('ord_sku_catid')));
}
//Now run a query that gets the ID's of the offending orders. You MUST
//order the records by the fields you are using to identify them as dupes.
list<ccrz__E_OrderItem__c> listorders = [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 IN :ord_ids
AND ccrz__product__r.ccrz__sku__c IN :ord_skus
AND ccrz__orderlinetype__c IN :ord_sku_types
AND ccrz__category__r.ccrz__categoryid__c IN :ord_sku_catids
ORDER BY ccrz__order__c, ccrz__product__r.ccrz__sku__c, ccrz__orderlinetype__c, ccrz__category__r.ccrz__categoryid__c];
//Remove non dupes
list<ccrz__E_OrderItem__c> realDupes = new list<ccrz__E_OrderItem__c>();
for(ccrz__E_OrderItem__c listorder : listorders) {
for(integer x = 0; x < badorders.size(); x++) {
AggregateResult ar = badorders[x];
if ((ID)ar.get('ord_id') == listorder.Id && string.valueOf(ar.get('ord_sku')) == listorder.ccrz__product__r.ccrz__sku__c && string.valueOf(ar.get('ord_sku_type')) == listorder.ccrz__orderlinetype__c && string.valueOf(ar.get('ord_sku_catid')) == listorder.ccrz__category__r.ccrz__categoryid__c) {
realDupes.add(listorder);
}
}
}
//Since we don't want to delete ALL the orders we just found (we have to leave one behind) we create another list
//of orders that will actually get deleted.
list<ccrz__E_OrderItem__c> ordersToDelete = new list<ccrz__E_OrderItem__c>();
//A variable to hold the subject of the last order looked at. If it matches, then it's a dupe.
//hence the reason we needed to sort by subject
string last_Id = 'placeholder123';
string last_ord_sku = 'placeholder123';
string last_ord_sku_type = 'placeholder123';
string last_ord_sku_catid= 'placeholder123';
for(ccrz__E_OrderItem__c realDupe : realDupes) {
//here is that logic I was talking about. If the current order name
//is the same as the one we just looked at, then obviously it's a dupe
//and should get deleted. Otherwise don't do anything cause it's the one order
//we want to leave behind.
if(realDupe.Id == last_Id && realDupe.ccrz__product__r.ccrz__sku__c == last_ord_sku && realDupe.ccrz__orderlinetype__c == last_ord_sku_type && realDupe.ccrz__category__r.ccrz__categoryid__c == last_ord_sku_catid) {
ordersToDelete.add(realDupe);
}
//set the variable for the next iteration
last_Id = realDupe.Id;
last_ord_sku = realDupe.ccrz__product__r.ccrz__sku__c;
last_ord_sku_type = realDupe.ccrz__orderlinetype__c;
last_ord_sku_catid = realDupe.ccrz__category__r.ccrz__categoryid__c;
}
//If there is anything to delete
if(!ordersToDelete.isEmpty()) {
//delete those suckers.
delete ordersToDelete;
}
3 answers