How to use SOQL aggregate functions to summarize data and avoid Too Many SOQL Queries: 101?
Here’s a code example that demonstrates how to use SOQL aggregate functions to summarize data and avoid the “Too Many SOQL Queries: 101” error in Salesforce:
Scenario:
Suppose you want to find the total number of Opportunities and the total Amount for each Account and update the corresponding fields on the Account record.
Read more: Loops – Salesforce Apex
Original Code (Without Aggregate Functions):
In this original example, a SOQL query retrieves all Opportunity records for each Account, which can lead to exceeding the governor limit if there are many Account records with numerous related Opportunities.
public class AccountOpportunityAggregator {
public static void updateAccountsWithOpportunityData(List<Account> accounts) {
for (Account acc : accounts) {
List<Opportunity> opportunities = [SELECT Id, Amount FROM Opportunity WHERE AccountId = :acc.Id];
acc.Total_Opportunities__c = opportunities.size();
Decimal totalAmount = 0;
for (Opportunity opp : opportunities) {
totalAmount += opp.Amount;
}
acc.Total_Opportunity_Amount__c = totalAmount;
}
update accounts;
}
}Optimized Code (Using Aggregate Functions):
In the optimized code, SOQL aggregate functions are used to calculate the total number of Opportunities and the total Amount directly in the query, reducing the number of records retrieved and avoiding the need for multiple queries.
public class AccountOpportunityAggregator {
public static void updateAccountsWithOpportunityData(List<Account> accounts) {
// Step 1: Collect all Account IDs
Set<Id> accountIds = new Set<Id>();
for (Account acc : accounts) {
accountIds.add(acc.Id);
}
// Step 2: Use aggregate functions to summarize Opportunity data
Map<Id, Account> accountMap = new Map<Id, Account>();
for (AggregateResult result : [SELECT AccountId, COUNT(Id) oppCount, SUM(Amount) totalAmount
FROM Opportunity
WHERE AccountId IN :accountIds
GROUP BY AccountId]) {
Id accountId = (Id)result.get('AccountId');
Account acc = new Account(
Id = accountId,
Total_Opportunities__c = (Integer)result.get('oppCount'),
Total_Opportunity_Amount__c = (Decimal)result.get('totalAmount')
);
accountMap.put(accountId, acc);
}
// Step 3: Update Accounts with aggregated data
List<Account> accountsToUpdate = new List<Account>();
for (Account acc : accounts) {
if (accountMap.containsKey(acc.Id)) {
Account aggregatedData = accountMap.get(acc.Id);
acc.Total_Opportunities__c = aggregatedData.Total_Opportunities__c;
acc.Total_Opportunity_Amount__c = aggregatedData.Total_Opportunity_Amount__c;
accountsToUpdate.add(acc);
}
}
update accountsToUpdate; // Perform a single DML operation
}
}In this optimized code, a single SOQL query with aggregate functions is used to retrieve the summary data for all Account records in the batch. This significantly reduces the number of queries and records processed, helping to avoid the “Too Many SOQL Queries: 101” error.
Read more: Collections in Salesforce Apex
