We are trying to create a mechanism that would process user dynamically created queries and create "Contributors" for each matching record.
The object model is as follows:
Account -< PO Number(s) -< PO Configurations
A PO Configuration is per Object Type (Shift, Expense, Timesheet, Deduction ect) and holds a JSON object that compiles into a SOQL query to match records. These configurations are created by an expressions UI that allows quite complex filters to be applied with AND/OR relationships between groups and conditions.
A PO Number can have multiple configurations for multiple different objects; the mechanism then expected to "walk through" each active PO Number on the system (potentially thousands) and execute all user-defined related queries - we expect a PO Number to have 5-10 such queries (possibly less).
When the mechanism executes the queries, it will create a "Burn Contribution" that links to the PO Number and keeps the id of the returned record in a text field.
For context, this is what the UI looks like:
And this is what the dynamic filter configurator looks like:
And this is what a Burn Configuration record looks like:
Now, as you might guess, we are playing around with potentially huge amounts of data and the necessity to execute SOQL within a for loop (in order to get all records for dynamically created configurations/conditions).
So the design is Calculator class is executed, assuming that we need to process all PO Numbers, we will launch a Batch Class that queries for all active PO Numbers and processes them 5 at a time. For each PO Number, we will launch a queueable that will execute 5 PO Configuration records at a time (i.e. at least 5 SOQL queries per queueable).
Class -> Batch (For PO Numbers) -> Queueable (for each configuration/soql)
Now, I know that we have 50 jobs limit and we can circumvent that by calling a queueable from a future and vice versa, but I am worried about the above design - we have yet to deal with queries that return hundreds of records within the queueable - I would ideally want to process them in Batches, but I am not sure how to call a batch with a dynamic SOQL query - generated within a for loop.
There are loads of limits that we can hit with the current design and although it works as a POC, I am perfectly aware that I need to re-think how to process all these records.
Lastly, we use Custom Metadata to define: What object is supported to create Burn Contributors, which field on that object provides the Burn Value, which field provides the date against which we match for PO Number suitability and finally which field provides Account matching.
My question is:
How would you best design the framework so that: a) You process hundreds of PO Numbers per operation (can be a nightly job) b) You assume that each PO Number has multiple PO Configs that result in multiple SOQL queries across mixed objects, not hardcoded c) You assume that each PO Config SOQL may return thousands of records to be processed d) You assume that the mechanism allows through configuration to add support for calculation for a new object (through Custom Metadata and then User adding conditions from UI) and thus the mechanism has to remain completely flexible.
This is how we build the dynamic SOQL query for even more context:
//Convert JSON condition object to Apex Class
SIM_Burn_Utils.Filter filter = SIM_Burn_Utils.convertFilterJSON(burnConfig.JSON_Data__c);
if(filter == null){
//Invalid JSON Data for Config - Do not process.
return;
}
//Get the relevant burn matcher by Id
SIM_PO_Burn_Matcher__mdt burnMatcher = mapMatchers.get(burnConfig.Burn_Matcher_ID__c);
//Build WHERE clause from conditions
String filterClause = SIM_FilterComponent.buildSOQLFilter(burnMatcher.Match_Object__r.QualifiedApiName, filter.crossGroupOperator, filter.groups);
system.debug('Built filterClause: ' + filterClause);
//Select fields to query
String stringSOQL = 'SELECT ID, ' +
burnMatcher.Date_Provider_Field__r.QualifiedApiName + ', ' +
burnMatcher.Value_Provider_Field__r.QualifiedApiName + ', ' +
burnMatcher.Account_Match_Provider__r.QualifiedApiName + ' ';
//Select dynamic object
stringSOQL += 'FROM ' + burnMatcher.Match_Object__r.QualifiedApiName + ' ';
stringSOQL += 'WHERE ' + filterClause + ' ';
//Add support for 15 and 18 character Id comparison. Needed where a formula returns an id
//E.g. Shift -> Client Id formula field is a text field of 15 chars - match burn contributor by client
Id tempVar0 = burnConfig.PO_Number__r.Account__c;
String tempVar1 = burnConfig.PO_Number__r.Account__c.to15();
stringSOQL += 'AND ' + '(' + burnMatcher.Account_Match_Provider__r.QualifiedApiName + ' =: tempVar0 OR ' + burnMatcher.Account_Match_Provider__r.QualifiedApiName + ' =: tempVar1) ';
Date tempVar2 = burnConfig.PO_Number__r.Start_Date__c;
Date tempVar3 = burnConfig.PO_Number__r.End_Date__c;
//Make sure we are within range
stringSOQL += 'AND ' + '(' + burnMatcher.Date_Provider_Field__r.QualifiedApiName + ' >=: tempVar2 AND ' + burnMatcher.Date_Provider_Field__r.QualifiedApiName + ' <=: tempVar3) ';
stringSOQL += 'LIMIT 5000';
system.debug('tempVar0: ' + tempVar0);
system.debug('tempVar1: ' + tempVar1);
system.debug('tempVar2: ' + tempVar2);
system.debug('tempVar3: ' + tempVar3);
system.debug('Built SOQL: ' + stringSOQL);
List<sObject> records;
try{
records = Database.query(stringSOQL);
system.debug('Retreived Records: ' + records.size());
}catch(Exception e){
system.debug('Error Querrying: ' + e.getMessage() + ' Stack: ' + e.getStackTraceString());
}
And this is how I create contributors for each identified record
for(Id configId : this.recordsPerBurnConfig.keySet()){
PO_Burn_Configuration__c burnConfig = this.burnConfigsMap.get(configId);
SIM_PO_Burn_Matcher__mdt burnMatcher = this.mapMatchers.get(burnConfig.Burn_Matcher_ID__c);
for(sObject record : this.recordsPerBurnConfig.get(configId)){
List<PO_Burn_Contributor__c> oldContributors = oldBurnContributorsMap.containsKey(record.Id) ? oldBurnContributorsMap.get(record.Id) : null;
//If the record already has contributors, overwrite the values
PO_Burn_Contributor__c oldContributor = null;
if(oldContributors != null){
for(PO_Burn_Contributor__c oldC : oldContributors){
if(oldC.PO_Burn_Configuration__c == burnConfig.id){
oldContributor = oldC;
break;
}
}
}
Decimal burnValue = (Decimal)record.get(burnMatcher.Value_Provider_Field__r.QualifiedApiName);
if(burnValue == null){
//If we already have a burn contributor for the record, queue to purge
if(oldContributor != null){
contributorsToDelete.add(oldContributor);
}
//Do not create a null value contributor
continue;
}
PO_Burn_Contributor__c newContributor = new PO_Burn_Contributor__c(
id = oldContributor != null ? oldContributor.Id : null, //Check if we need to update the record
PO_Number__c = burnConfig.PO_Number__c,
Date__c = (Date)record.get(burnMatcher.Date_Provider_Field__r.QualifiedApiName),
Value__c = burnValue != null ? burnValue : 0,
Is_Forecast__c = burnMatcher.Is_Forecast__c,
Record_ID__c = record.id,
PO_Burn_Configuration__c = burnConfig.id
);
system.debug('Creating new contributor: ' + newContributor);
contributorsToUpsert.add(newContributor);
}
}