Thank you Zach,
I’m working on a javascript function that takes as a parameter any model (basic or aggregate) and loads all records in that model one limited query at a time to prevent heap size errors from salesforce from having too large queries (sort of like load all remaining records, but without the 10,000 record limit and works on aggregate models)
I actually have the function fully written out and tested and it’s working great. I just wanted to be sure about the as you say “fringe” display types as it relies on the display type to infer whether or not to enclose a model field in quotes.
I’ll likely make another forum post once I’m more confident in this function working. I’ll post it here for your review now though. Let me know if you think I might be missing anything or have any questions as to the logic.
To make this work you just do an in-line with the following code in your page:
var $ = skuid.$;
//skuid.model.modelLoader(model,fparams);
//Clear and Load all records in a model in chunks limited by the model's limit propery, a passed limit, or otherwise if both are undefined defaults to 200.
//Model can be Basic or Aggregate, both will load in chunks limited by the limit.
//Runs Asynchronously and works with $.when();
//fparams: object
//{
// limit: Number of rows to limit by. If unspecified will choose the model's recordsLimit property or if that is unspecified defaults to 200
// progressCallback: function to call before running each individual query in the format progressCallback(fparams), fparams is an object
// progressCallback fparams = {
// count: count of rows queried so far
// limit: our limit for how many rows to query per query run
// nextStart: the row # of the next row to be queried,
// nextEnd: the last row # to be queried (based on limit)
// }
//}
skuid.model['modelLoader'] = function(model,fparams){
var deferred = $.Deferred();
if(model===undefined){
deferred.reject('Model undefined');
return;
}
var fparams = fparams || {};
var limit = fparams['limit'] || model['recordsLimit'] || 200;
var progressCallback = fparams['progressCallback'] || undefined;
//Set Initial Progress
if(progressCallback !== undefined){
progressCallback.call(this,{
count: 0,
limit: limit,
nextStart: 1,
nextEnd: limit
});
}
model.abandonAllRows();
//Run through our recursive function to load all rows
modelLoadSOQL(model,{
limit: limit,
progressCallback: progressCallback,
promiseResolve: deferred.resolve,
promiseReject: deferred.reject
});
//limit,last,count
function modelLoadSOQL(model,fparams){
var fparams = fparams || {};
var limit = fparams['limit'] || model['recordsLimit'] || 200;
var last = fparams['last'] || undefined;
var count = fparams['count'] || 0;
var progressCallback = fparams['progressCallback'] || undefined;
//var promise = fparams['promise'] || undefined;
var promiseResolve = fparams['promiseResolve'] || undefined;
var promiseReject = fparams['promiseReject'] || undefined;
var displayTypesToEnclose = ['STRING','ID','TEXT','TEXTAREA','PICKLIST','MULTIPICKLIST','PHONE','REFERENCE','URL','EMAIL','ADDRESS','ENCRYPTEDSTRING'];
var displayTypesDontEnclose = ['DOUBLE','BOOLEAN','CURRENCY','DATE','DATETIME','INTEGER','PERCENT'];
var queryStr = model.soql;
var orderby = '';
//Get rid of SELECT, we'll add that ourselves
queryStr = queryStr.replace('SELECT ', '');
//Get rid of LIMIT, we'll add that ourselves
queryStr = queryStr.replace(/ LIMIT .*/i, '');
//Get rid of ORDER BY, we'll add that ourselves, will either be our group by fields (aggregate model), or simply "Id"
queryStr = queryStr.replace(/ ORDER BY .*/i, '');
//Get rid of and save HAVING, we'll add this back
var having = queryStr.match(/( HAVING .*)/i);
if(having!==null && having.length > 1){
having = having[1];
}
else{
having = '';
}
queryStr = queryStr.replace(/ HAVING .*/i, '');
//Get rid of and save GROUP BY, we'll add this back
var groupby = queryStr.match(/( GROUP BY .*)/i);
if(groupby!==null && groupby.length > 1){
groupby = groupby[1];
}
else{
groupby = '';
}
//Get the items that are being grouped by
var groupByItemsStr = groupby.match(/ GROUP BY (.*)/i);
if(groupByItemsStr!==null && groupByItemsStr.length > 1){
groupByItemsStr = groupByItemsStr[1];
}
else{
groupByItemsStr = '';
}
var groupByItemsArr = groupByItemsStr.split(',');
for(let i=0;i<groupByItemsArr.length;i++){
groupByItemsArr[i] = groupByItemsArr[i].trim();
}
queryStr = queryStr.replace(/ GROUP BY .*/i, '');
var lastRowConditions = '';
//Basic Model
if(!model.isAggregate){
if(last !== undefined){
lastRowConditions = '(Id > ''+last['Id']+'')';
}
orderby = ' ORDER BY Id';
}
//Aggregate Model
else{
//Set up our order by string
for(let i=0;i<groupByItemsArr.length;i++){
if(orderby!=''){
orderby+=',';
}
orderby+=groupByItemsArr[i];
}
if(orderby != ''){
orderby = ' ORDER BY '+orderby;
}
//If we have a last row, set up our last row conditions
if(last !== undefined){
lastRowConditions = '';
var lrcArr = [];
var keyToNameMap = new Map();
//Loop through our last row fields finding grouped fields to condition on
for(let [key, value] of Object.entries(last)){
//Ignore attributes and non-grouped fields
var aggFieldId = getAggFieldIdFromName(model,key);
if(key != 'attributes' && aggFieldId !== undefined && groupByItemsArr.includes(aggFieldId)){
var obj = {};
obj[aggFieldId] = value;
lrcArr.push(obj);
keyToNameMap.set(aggFieldId,key);
}
}
//while we still have group by fields to condition on, loop
//constructing conditions to aggregately query the remaining rows we have not yet queried
//pop the last element in lrcArr after each iteration
while(lrcArr.length > 0){
var orCondition = '';
//add OR if not the first condition
if(lastRowConditions != ''){
orCondition = ' OR ';
}
var dontQueryNextBoolean = false;
var thisCondition = '';
var first = true;
//loop through our last row group by fields
$.each(lrcArr,function(i,row){
//If we're at the last field, set greater than condition
if(i==lrcArr.length-1){
//Loop through the fields
for(let [key, value] of Object.entries(row)){
let enclose = false;
if(displayTypesToEnclose.includes(model.fieldsMap[key]['displaytype'])){
enclose = true;
}
if(value === null || value === 'null'){
if(enclose){
value = '';
}
else{
value = 'NULL';
}
}
else if(value === true){
value = 'true';
}
else if(value === false){
value = 'false';
}
//Ignore if boolean true
if(model.fieldsMap[key]['displaytype'] != 'BOOLEAN' || (model.fieldsMap[key]['displaytype'] == 'BOOLEAN' && value == 'false')){
if(!first){
thisCondition += ' AND ';
}
if(model.fieldsMap[key]['displaytype'] == 'BOOLEAN'){
thisCondition += key + ' = true';
}
else if(enclose){
thisCondition += key + ' > ''+value+''';
}
else{
thisCondition += key + ' > '+value;
}
}
else if((model.fieldsMap[key]['displaytype'] == 'BOOLEAN' && value == 'true')){
dontQueryNextBoolean = true;
}
}
}
//If we're not at the last field, set equal condition
else{
for(let [key, value] of Object.entries(row)){
let enclose = false;
if(displayTypesToEnclose.includes(model.fieldsMap[key]['displaytype'])){
enclose = true;
}
if(value === null || value === 'null'){
if(enclose){
value = '';
}
else{
value = 'NULL';
}
}
if(!first){
thisCondition += ' AND ';
}
if(enclose){
thisCondition += key + ' = ''+value+''';
}
else{
thisCondition += key + ' = '+value;
}
}
}
first = false;
});
//add to our lastRowConditions
if(thisCondition != '' && !dontQueryNextBoolean){
if(lastRowConditions == ''){
lastRowConditions = '(';
}
lastRowConditions += orCondition+'('+thisCondition+')';
}
//pop the last field to condition on
lrcArr.pop();
}
if(lastRowConditions != ''){
lastRowConditions += ')';
}
}
}
//If we have Last Row conditions, need to add AND if there is a WHERE
if(lastRowConditions != '' && queryStr.includes(' WHERE ')){
lastRowConditions = ' AND '+lastRowConditions;
}
//If there is no where, add WHERE
else if(lastRowConditions != ''){
lastRowConditions = ' WHERE '+lastRowConditions;
}
var queryStr = 'SELECT ' + queryStr + lastRowConditions + groupby + having + orderby + ' LIMIT '+limit;
//console.log('QUERY:');
//console.log(queryStr);
skuid.$.when(skuid.sfdc.api.query(queryStr)).done(function(queryResult) {
//Records is an array of query results in the format {field: value, field2: value2}
var records = queryResult.records;
var thisLastRow = undefined;
if(records !== undefined && records !== null && records.length > 0){
for(let i=0;i<records.length;i++){
//Remove attributes, we won't adopt that field into our rows
if(records[i]['attributes'] !== undefined){
delete records[i]['attributes'];
}
}
//Adopt our rows into our model
model.adoptRows(records);
}
//If we hit our query limit, we need to query again
if(records.length == limit){
if(progressCallback !== undefined){
progressCallback.call(this,{
count: count,
limit: limit,
nextStart: count+records.length+1,
nextEnd: count+(records.length*2)
});
}
modelLoadSOQL(model,{
limit: limit,
last: records[limit-1],
count: count+records.length,
progressCallback: progressCallback,
promiseResolve: promiseResolve,
promiseReject: promiseReject
});
}
//Otherwise we've finished querying, resolve
else{
try{
promiseResolve();
}catch(err){
console.error(err);
}
}
}).fail(function(queryResult) {
promiseReject(queryResult.error);
});
function getAggFieldIdFromName(model,name){
for(let i=0;i<model.fields.length;i++){
if(model.fields[i]['function']===undefined && model.fields[i]['name'] == name){
return model.fields[i]['id'];
}
}
return undefined;
}
}
return deferred.promise();
}
