How to do this in one SOQL

Question

I have a query where I need to retrieve a list of Accounts and their related childs "Commission Imports", but I only need the latest of these. I determine which is the latest based on two fields: "Process Date" and "Revenue Date", in that order. In case there are multiple Commission Imports with same Process and Revenue Date I need to retrieve all of them.

I was initially doing this by retrieving all the childs, ordered, but it turns out some Accounts have quite a lot of them, and it's became inefficient to retrieve everything when I'm only gonna need one, two or three of them.

My original query

SELECT Id, (SELECT Id FROM Commissions_Imports__r ORDER BY Process_Date__c DESC, Revenue_Date__c DESC)
FROM Account WHERE Id IN :accounts

I don't have a max possible number of matching child records so I cannot just use a limit, I need to be able to tell for each Account which are their Commission Imports matching both Dates and retrieve only those.

Any idea on how this can be done in one or two SOQL queries?

Thank you!

Answer 1

You can't do this straight in SOQL, at least not without some help.

To get there, we'll make two rollup summary fields, one for Max Process Date, and one for Max Revenue Date. If the records aren't master-detail, DLRS can help. Now, we'll make an account formula field that just gives us the higher value, written as:

Max_Date__c (Return Type: Date)

IF(Max_Process_Date__c > Max_Revenue_Date__c, Max_Process_Date__c, Max_Revenue_Date__c)

Now, on the child object, we just use a simple formula:

Is_Latest__c (Return Type: checkbox)

Account__r.Max_Date__c = Max_Process_Date__c ||
Account__r.Max_Date__c = Max_Revenue_Date__c

Now, our SOQL is ready:

SELECT (SELECT Id FROM Commission_Reports__r WHERE Is_Latest__c = true)
FROM Account WHERE Id = :accounts

In case I misunderstood your question, the general solution still applies. Create rollup summary fields on the account, and then use formulas on the child records to narrow down the records you retrieve.

Thanks for @DerekF mentioning that you probably want:

SELECT Account__c Id, SUM(Commission_Received__c) sum
FROM Commission_Report__c 
WHERE Account__c IN :accounts AND Is_Latest__c = true 
GROUP BY Account__c

Answer 2

Another approach that's situated between the two existing ones (rollup to the account + some formula fields, and sort ascending + overwrite map values) could be to use an extra query + some extra heap space to reduce the amount of rows/work that SOQL needs to process.

Since Process_Date__c takes priority here, if you figure out what the most recent date is per account then you can (with the help of a formula field) ignore the rest of the child records on that that don't have that date.

"Account_plus_date__c" (formula field on Commissions_Import__c, Return Type: "Text")

Provider_Account_relationship__c & '-' & TEXT(Process_Date__c)

Important note here: TEXT() always turns a date into "YYYY-MM-DD" format, regardless of the user's locale.

// Step 1: figure out the max date per account, and store that information
Set<String> accountMaxDates = new Set<String>();

// We can LIMIT 1 here in the subquery to reduce the number of rows returned
// We're just interested in getting the AccountId + date combo here
for(Account acct :[SELECT Id, (SELECT Id, Process_Date__c FROM Commissions_Imports__r ORDER BY Process_Date__c DESC LIMIT 1) FROM Account WHERE Id IN :accounts]){
    // Taking care to match the "YYYY-MM-DD" format for the date and
    //   being careful not to mix local time & GMT/UTC processing
    accountMaxDates.add(String.format('{0}-{1}', new List<String>{
        acct.Id,
        // Directly accessing the child record list like this is ok here because
        //   the "LIMIT 1" ensures we will never encounter a "queryMore"
        Datetime.newInstance(acct.Commissions_Imports__r.get(0)?.Process_Date__c, Time.newInstance(0, 0, 0).format('YYYY-MM-DD')
    }));
}

// Step 2: Do the summation only for the account-date pairs we previously found
//   (to reduce the work and rows that this query needs to process)
Map<Id, Account> accountsToUpdate = new Map<Id, Account>();

// Generally better to use `__c` rather than `__r.Id`
// If Salesforce complains about query selectivity, then just capture the Account Ids
//   in a separate collection in the loop above and then add it as a filter to this query
// We should only need to order by revenue date here (since the previous step already
//   got our target process dates)
for(AggregateResult agg :[SELECT Provider_Account_relationship__c, SUM(Commission_Received__c) commission FROM Commissions_Import__c WHERE Account_plus_date__c IN :accountMaxDates GROUP BY Provider_Account_relationship__c ORDER BY Revenue_Date__c ASC){
    Id accountId = (Id)agg.get('Provider_Account_relationship__c'));
    accountsToUpdate.put(accountId, new Account(
        Id = accountId,
        Last_Commission__c = (Double)agg.get('commission')
    ));
}

You could also ORDER BY Revenue_Date__c DESC and then skip the row if the Account has already been seen instead

for(AggregateResult agg :[SELECT Provider_Account_relationship__c, SUM(Commission_Received__c) commission FROM Commissions_Import__c WHERE Account_plus_date__c IN :accountMaxDates GROUP BY Provider_Account_relationship__c ORDER BY Revenue_Date__c DESC){
    Id accountId = (Id)agg.get('Provider_Account_relationship__c'));
    if(accountsToUpdate.containsKey(accountId)){
        // skips the rest of this iteration and moves to the next result row
        continue;
    }

    accountsToUpdate.put(accountId, new Account(
        Id = accountId,
        Last_Commission__c = (Double)agg.get('commission')
    ));
}

The tradeoffs here are:

• you're burning an extra query
  • but you'd likely use more queries if you used DLRS or a rollup summary field, so this is likely query neutral (or maybe even net negative) compared to that approach
  • still one more query than the "sort asc + overwrite map" approach
• you're using a bit of extra heap space to store the Account Ids + dates
  • but it keeps everything on Commissions_Import__c
  • and it reduces the number of rows that need to be queried/processed (though not as much as the rollup + 2 formula approach does)

Answer 3

Thank you so much @DerekF and @sfdcfox for your answers! I just want to share another approach I figured out could also work, though probably yours would be better in the sense that it'd take off the load from the apex trigger, but please let me know what do you think anyways...

AggregateResult[] test = [SELECT Provider_Account_relationship__r.Id, SUM(Commission_Received__c) FROM Commissions_Import__c WHERE Account_relationship__r.Id IN :accounts GROUP BY Account_relationship__r.Id, Process_Date__c, Revenue_Date__c ORDER BY Process_Date__c ASC, Revenue_Date__c ASC];

Map<Id, Double> AcctWLastComm = new Map<Id, Double>();

for(AggregateResult r : test){
    AcctWLastComm.put(r.Id, Double.valueof(r.get('expr0')));
}
System.debug('result: ' + AcctWLastComm);

In the initial SOQL I'm using the same list of accounts but now I'm getting the total SUM of Commission Received for each group; and I'm grouping them by their related Account (a custom field already in the Commission Imports), then by their Process and Revenue Dates to get just one entry per matching Acct/Process Date/Revenue Date. After storing this on a list of AggregateResults I'm iteratin over the list to finally save the values to a Map of Ids (of the Acct) and Doubles (the result of the commission received sum). Here, since they're ordered by both dates, I expect to have only the most recent for each Acct, as the newer should replace the older one if it exists.

Thanks again for your help, and of course I'm still open to suggestions.