1

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!

4
  • Do you need any information from any of these "Commision Import" records, or are you only looking for a count of these records per Account? I guess another way to put that question is "what are you trying to do with this data once you get it?"
    – Derek F
    May 2, 2023 at 15:24
  • Also, is the "inefficiency" here really an issue? As in, is it causing you to go over (or get uncomfortably close to) a governor limit? Is it causing you to be able to process fewer records than you want/need? If not, then it's probably not worth your time to optimize here (and what, exactly, are you trying to optimize? What thing are you measuring?)
    – Derek F
    May 2, 2023 at 15:27
  • Hello Derek. Yes, actually this query in specific is taking a very long amount of CPU time, and in total I'm getting very close to the 50,000 max rows in a transaction. May 2, 2023 at 15:42
  • And yes, I actually only need one value, the "Commission Received" (I'm actually summing up all Commission Received values from matching records at the end). May 2, 2023 at 15:44

3 Answers 3

3

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
6
  • 1
    I was going to suggest something similar. The main difference would be that I'd suggest using a query to do the summation of "Commission Received" that OP mentioned in the comments (SELECT AccountId, SUM(Commission_Received__c) FROM Commission_Report__c WHERE AccountId IN :accounts AND Is_Latest__c = true GROUP BY AccountId)
    – Derek F
    May 2, 2023 at 16:17
  • @DerekF Yeah, that'd work, too, I was just going off their original query. I'll edit that that in, though.
    – sfdcfox
    May 2, 2023 at 16:20
  • When I was considering answering, what worried me was the equivalent of your "max date" formula field that I was going to add. I think this doesn't do what is needed. I was going to suggest adding an orderable text field that was basically a concatenation of the two dates as a string, TEXT(Max_Process_Date__c) + '/' + TEXT(Max_Revenue_Date__c). All commission reports sharing the maximum of this string value can then be selected. However, you actually need different max combos per account to avoid spurious additional matches across the accounts.
    – Phil W
    May 2, 2023 at 16:55
  • (The latter concern probably evaporates with the rollups, though I think you still need to have that pair of values as an orderable string like I suggested)
    – Phil W
    May 2, 2023 at 16:57
  • @PhilW They did say "latest of two dates", which is what I derived my solution from. The SOQL disagrees with the text. I find that the text of the business requirement is usually more accurate than the code attempted in these kinds of circumstances.
    – sfdcfox
    May 2, 2023 at 17:01
1

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)
0

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.

1
  • This approach would likely work, though because the query engine still has to sort through the same number of records as your initial solution (even if it isn't counted against you in the query rows limit) it'll probably run no faster in "clock time" (Salesforce claims that time spent in the database doesn't count towards the CPU limit). This is also limited to 2,000 rows (after which it'd try to issue an internal call to queryMore() and you'd get an error).
    – Derek F
    May 2, 2023 at 17:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .