Apply "Row Limit" configuration value to the resulting SOQL query

[vca-ableton]

Is your feature request related to a problem? Please describe. The records that I am summarizing have begun to hit the 50001 rows limit. I noticed that many of my aggregates were to get the first or last record from a sorted list. I thought the "Row Limit" feature would update the SOQL query and reduce the number of returned records, but it seems to only be applied in the aggregate processing.

Describe the solution you'd like I realize that applying the "Row Limit" might lead to the SOQL query might lead to unexpected results, but I'd like to have the option at least to do this.

Describe alternatives you've considered I've considered writing my own Apex or Flows to migrate the aggregates, but I like being able to see everything in DLRS's UI panel.

Additional context N/A

[aheber]

@vca-ableton This is a great feature request. I had always assumed that "First" would limit the resulting dataset to a single record but after a very quick look through the code I don't think it is doing that today, as you mention. "Last" wouldn't be able to be limited and still provide a consistent result but if you could order by that field such that your desired last would actually be First then we could do a single row as a "First" rollup.

I'll look at what changes would have to be executed to deliver this. I don't know how soon it could get done if it is in my backlog but you're welcome to take a look at see what it would take and I can give you some steps to help get to the right place.

Really, even building Apex Tests that demonstrated the desired functionality would go a long way for getting it clearly defined and delivered.

I don't know if we'd seriously look at applying the Row Limit to all queries, you're right it could cause unexpected results and that sort of behavior would need a much bigger discussion and review. For now I'd focus on getting "First" to be able to limit the number of rows it pulls out of the database and that might be enough that you can take that as a starting point to resolve some of your problems. I know it doesn't immediately solve all possible problems.

If you're not comfortable trying to approach this don't worry about it. I can't promise it will get done soon but I think this is a great one for the backlog and maybe one of the regular dev's would be able to work on it sooner than later.

[vca-ableton]

Hi @aheber , thank you for your feedback and thoughts on the feature! I agree that the workaround to get the "last" element first would suffice for my use cases. I actually already had formula fields to manage that. I'm keen to look into the Apex and contribute what I can. I'll follow up here with what I find.

[aheber]

I'd start here, https://github.com/SFDO-Community/declarative-lookup-rollup-summaries/blob/main/dlrs/libs/lrengine/classes/LREngine.cls

LREngine is designed to be able to execute multiple rollups with a single aggregate query. First, Last, and a few others step out of that anyway but still share some of that logic. That goal is probably why it doesn't respect the row limit in the query. "What if I need those rows for another rollup" kind of thinking.

You can probably get away with some kind of "count" logic, if I'm running more than one rollup then I can't take this shortcut but if I'm only running a single rollup and that rollup is a "First" type of rollup then take the shortcut.

You can see where it would fit but that would be my thinking prior to investigation.

Feel free to ping me with thoughts or questions.

[vca-ableton]

Thank you! I have an understanding of where to update the Context class to check if the operation is "First" and update the query template. However, I realized I hadn't thought this fully through - as I'm running DLRS in a batch job, I think I would need the first row for every master ID in the query list :-(. I have a hunch I could re-arrange the query to start at the master level and at a row limit to a nested child query like, "SELECT Id, (SELECT Id FROM ChildRecords WHERE LIMIT 1) FROM MasterRecord WHERE Id IN :masterIds" but imagine that would be a much larger effort to refactor.

I'm interested in trying it though :-). Do you have any documentation or advice on deploying an unpackaged version into an org to test? I'm a little rusty on deploying projects this large.

[aheber]

@vca-ableton I see what you're saying, this might turn into a very complicated refactor.

For setting up a dev environment, you can take a look here https://sfdo-community-sprints.github.io/DLRS-Documentation/About%20Us%20&%20Contribution/dev.html that should be a good jumping off point.