This post is all about how to use aggregate query effectively or write less complex apex code.
Use Case: Lets say you need to update latest case record of each opportunity and you have been given list of opportunity Ids. One opportunity can have multiple cases. How can you do it?
Approach - 1 If you are strong in SQL, you might be linking to use Group By Opportunity__c Order By CreatedDate Desc and Limit 1. SOQL does not support Order By limit along with Group By.
Approach - 2 Using Map to hold Opportunity Id with latest Case
Here is sample code
// List of Opportunity IDs
List<Id> opportunityIds = new List<Id>{'oppId1', 'oppId2', 'oppId3'};
// Map to hold the latest Case for each Opportunity
Map<Id, Case> latestCaseMap = new Map<Id, Case>();
// Query all cases related to the Opportunity IDs,
sorted by CreatedDate descending to get the latest case first
List<Case> cases = [
SELECT Id, Opportunity__c, Subject, Status, CreatedDate
FROM Case
WHERE Opportunity__c IN :opportunityIds
ORDER BY CreatedDate DESC
];
// Loop through the cases and store the latest case for each Opportunity ID
for (Case c : cases) {
Id oppId = c.Opportunity__c; // Assuming 'Opportunity__c'
is the lookup field on the Case object
// If this Opportunity ID is not already in the map, add the current case
as the latest case
if (!latestCaseMap.containsKey(oppId)) {
latestCaseMap.put(oppId, c);
}
// Since the cases are already sorted by CreatedDate DESC, the first case
encountered is the latest one,
// so no need to process further cases for the same Opportunity.
}
// Now latestCaseMap contains each Opportunity ID and its corresponding latest Case.
// You can iterate over it and perform the necessary update for each latest Case
List<Case> casesToUpdate = new List<Case>();
for (Id oppId : latestCaseMap.keySet()) {
Case latestCase = latestCaseMap.get(oppId);
// Perform any necessary updates on the latest case
latestCase.Status = 'Closed'; // Example: updating the status to 'Closed'
// Add the case to the list for DML update
casesToUpdate.add(latestCase);
}
// Update the latest cases for each Opportunity
if (!casesToUpdate.isEmpty()) {
update casesToUpdate;
}
System.debug('Updated the latest case for each Opportunity.');
We can accomplish requirement in approach -2, but this is not efficient one.
Approach - 3 Using Aggregate Query.
Set<Id> caseIds = new Set<Id>();
List<Case> caseUpdateList = new List<Case>();
Case caseTemp;
// Query to get one Case ID per Approved_Lead__c (Opportunity)
List<AggregateResult> caseList = [
SELECT Approved_Lead__c, Max(Id) caseId
FROM Case
WHERE Approved_Lead__c IN :oppyIds
GROUP BY Approved_Lead__c
];
// Iterate through the results
for (AggregateResult ar : caseList) {
Id opportunityId = (Id) ar.get('Approved_Lead__c'); // Opportunity ID (Approved_Lead__c)
Id caseId = (Id) ar.get('caseId'); // The Case ID (MIN or MAX)
caseIds.add(caseId);
// Now you have the Opportunity ID and the Case ID
System.debug('Opportunity ID: ' + opportunityId + ' Latest Case ID: ' + caseId);
}
for(Id cseId: caseIds){
caseTemp = new Case(Id=cseId, Status='Closed');
caseUpdateList.add(caseTemp);
}
If(!caseUpdateList.IsEmpty()){
Update caseUpdateList;
}
Comments