model.fieldsMap.Field.encloseInQuotes / displayType -- List of all possible field displaytypes?

  • 1
  • Question
  • Updated 5 days ago
  • In Progress
Is there a list available anywhere of all possible displaytypes for fields on a model? I have custom logic that requires me to know whether or not to enclose a field in quotes when querying via SOQL, but the encloseInQuotes field under model fields exists inconsistently, sometimes being not defined on the field, and other times being defined. As an alternative strategy I'm inferring encloseInQuotes from the field displaytype, but I'm not sure if I have all displaytypes in my logic. Is there a list of them somewhere? Or is there some other way I can figure out the encloseInQuotes value seeing as it is inconsistently defined on the model fields?

Thanks!
Photo of Mark L

Mark L

  • 2,616 Points 2k badge 2x thumb

Posted 2 months ago

  • 1
Photo of Zach McElrath

Zach McElrath, Employee

  • 56,134 Points 50k badge 2x thumb
Hey Mark, the "encloseInQuotes" property on Model fields is not documented and should not be relied upon.

Would be interested to hear more about what exactly you're working on here.

As far as which Display Types require being enclosed in quotes, this won't cover all of the fringe types (e.g. LOCATION) but this should be the majority of them:

Require Quotes:

  • Text types: TEXT, STRING, TEXTAREA, COMBOBOX, PICKLIST, EMAIL, URL, MULTIPICKLIST, ENCRYPTEDSTRING, PHONE
  • Id types: ID, REFERENCE
Do NOT Require Quotes:

  • Number types: INTEGER, CURRENCY, PERCENT, DOUBLE
  • BOOLEAN
  • Date types: DATE, DATETIME
Photo of Mark L

Mark L

  • 2,616 Points 2k badge 2x thumb
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();
}

Photo of Zach McElrath

Zach McElrath, Employee

  • 56,134 Points 50k badge 2x thumb
Mark, I'm impressed you've been able to make this work but I'd like to understand more about why you needed to write this. You mentioned this as the motivation:

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

In my mind you really shouldn't need to write a function like this with Skuid, this is what Skuid (ideally) exists to prevent you from needing to do --- but it seems like you're running into two issues:

(1) you can't "load more" than 10,000 remaining records 
(2) the "load all remaining records" approach doesn't work for Aggregate Model load mores

Is that correct?

To address this, your solution is to:

(1) for non-aggregate models --- use an Id-ordering based approach to ensure that you're only loading additional records you haven't loaded yet.

(2) for aggregate models --- you're using the "ORDER BY" field on the aggregate model, or examining the grouping fields, to ensure that you are only querying records that you haven't seen yet.

I'm bummed that you are having to write this code in order to work around these problems --- we really should address these issues in the product to prevent you from having to do this.

One big concern I have with building this query client-side and using skuid.sfdc.api.query() is that you're avoiding all of Skuid's object-level and field-level security enforcement logic which is performed server-side as part of Skuid Model loads and our Apex logic. 

But my bigger concern is that you're having to write your own query builder logic to work around a Skuid limitation. 

I'm going to raise this concern with our product management team --- I'll try to post back here when I get an update.
Photo of Mark L

Mark L

  • 2,616 Points 2k badge 2x thumb
Hi Zach,

Note that I'm using Skuid v12.1.7 and API v1 on my pages.

The Load All Remaining Records function limits to 10,000 rows. If you go to a Table and try to export a CSV with the "load all remaining records" checkbox checked for a model that has more than 10,000 rows when fully loaded that's probably the easiest way to see it happen.

In terms of aggregate models, you can't limit the aggregate model and load the remaining records; when you try to do this you get a query error from Salesforce: Error: Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch. It also seems to not respect the LIMIT set in the UI at that point, ie. if you set a limit of 200 it seems to set LIMIT 1000 in the query it attempts, but the query it attempts will not work because in Salesforce aggregate queries do not support queryMore, and you'd need a different strategy to query limited aggregate data in a sequential manner.

The logic behind my design to get around the aggregate query limitation and allow aggregate queries to load X records at a time sequentially uses LIMIT alone and requires the queries to be ordered by the group by fields (in order of the group by fields as they are queried)

Here's the query logic that will get added when making sure to query the next X records from a model that is grouping by GroupByFieldA, GroupByFieldB, GroupByFieldC and ordered by GroupByFieldA, GroupByFieldB, GroupByFieldC

AND
(
(GroupByFieldA = 'LastRowValueA' AND GroupByFieldB = 'LastRowValueB' AND GroupByFieldC > 'LastRowValueC')
OR
(GroupByFieldA = 'LastRowValueA' AND GroupByFieldB > 'LastRowValueB')
OR
(GroupByFieldA > 'LastRowValueA')
)

The LastRowValues are the values from the last row that was queried in the previous query iteration. This ensures that only the next X rows can be queried when running a query with a limit of X. For booleans there's a little more logic involved since you can't do TRUE > FALSE in SOQL, but the same sort of principle applies.

In terms of the issue you brought forth for security, I could use dynamic models instead of the approach I took to do this, but it may be more difficult to construct the dynamic model. At the moment I'm not sure what I can rely on from the model object to go about essentially "copying" it and creating a new model with slightly different conditions. Also it would be a "temporary" dynamic model and I'm not sure what memory cleanup I might need to do with that. Also I noticed that this direct SOQL query approach is VERY fast to query, I'm not sure if that's inherent in using that function or something else, but it may be faster than a regular model load. Do you have any suggestions for how to go about copying the model into a new temporary dynamic model (with different conditions to make the sequential querying work) and cleaning up the temporary model we create when done querying on it?

Thanks!
(Edited)
Photo of Zach McElrath

Zach McElrath, Employee

  • 56,134 Points 50k badge 2x thumb
Thanks Mark, your approach to doing Aggregate Model "load more" is very well thought-out, this is a hard problem to solve!

As far as the speed of direct query vs Skuid Model loads, the main reason that Skuid Model loads are somewhat slower than direct REST API calls is due to the schema checks we make server-side to ensure that only accessible fields / objects are being queried. We continue to seek for ways to optimize this in every release, and we made some huge strides with Model load performance in Spark Update 3, especially in orgs with complex schemas.

As far as how to generate a dynamic Skuid Model, this article gives an example of doing that:

https://docs.skuid.com/latest/en/skuid/javascript/dynamic-model-component-creation.html#dynamic-creation-of-models

As long as you don't call .register() on the dynamic Model, there's no memory cleanup necessary, but even if you do .register() the Model (which is only necessary if you want to have Components / other Models be able to reference the dynamic Model), you can always call .unregister() the Model when you're done with it.

The doc doesn't mention creation of dynamic Aggregate Models, but here would be an example of that:

var aggregationFields = [
    { id: 'Id', "function": 'COUNT', name: "countId" },
];
var groupingFields = [
    { id: 'Account.Industry', name: "accountIndustry" },
    { id: 'StageName', name: "stage" },    
];
var orderBy = "COUNT(Id) DESC";
var objectName = "Opportunity";

var aggModel = new skuid.model.Model(`<model sobject="${objectName}" type="aggregate" orderby="${orderBy}" datasource="salesforce">
    <fields>
        ${aggregationFields.map(f => `<field id="${f.id}" function="${f.function}" name="${f.name}"/>`).join("")}
    </fields>
    <groupby>
        ${groupingFields.map(f => `<field id="${f.id}" name="${f.name}"/>`).join("")}
    </groupby>
</model>`
);
console.log(aggModel);
return aggModel.initialize().load().then(function() {
   console.log("aggregate results", aggModel.getRows());
});




Photo of Mark L

Mark L

  • 2,616 Points 2k badge 2x thumb
Thanks Zach,

I've been copying the existing XML of models in the editor UI and modifying the XML to construct dynamic models. I guess I was wondering if there was an easy way of going about having a javascript function do that (take an existing model that is passed and replicate it into another model with modified conditions); I guess my approach would be to just analyze the model object itself for the fields, conditions, and other variables there and replicate that in the dynamic model that is constructed. It's good to know that there won't be any memory cleanup necessary.

I also noticed that the technique I'm using in the code I posted doesn't seem to take into account the active/inactive conditions changed after the model was created. It looks like the model.soql doesn't update with later activated conditions. Also I should probably preserve model ordering, so I'll need to sort the data after the query completes based on the original ordering specified. I'll need to do some more work to get this fully coded out.

I may also use this technique to make a separate createRows function, as adoptRows doesn't seem to let you insert manually created row objects {} (the rows get a skuid temporary ID, but inserting them you get an invalid ID error) I might try creating a temporary model and doing a normal createRow on that model for each row I want to add then do an adoptRows from that model to the main model I want to createRows in. That should alleviate the issue of running through lots of createRow operations in a loop on a model that already has had a rendered component in the UI causing the UI to freeze. Hopefully rows adopted in this way can be properly inserted to the database.

One question I have though. I've never been able to get client-side searching / filtering to work on tables and other UI elements. That may be beneficial to use over server-side for any components I've fully loaded using my technique. Do you have any idea what I could do there to alleviate that issue either to get client-side searching / filtering to work properly, or overriding it somehow?

Thanks!
Photo of Mark L

Mark L

  • 2,616 Points 2k badge 2x thumb
I've made some major updates to my modelLoader function.

It now works off of the model's data rather than the model.soql (soql string) to formulate its query, which means that if you've enabled / disabled filterable conditions the loading will take that into account.

It also will sort the results of the load based on your model's Order By conditioning prior to adopting the rows into your model.

It also takes 2 new parameters in its parameters object, exportWhenDone (true/false) indicating whether or not when finished loading an export should be done, and exportOptions, optional options object to pass to the export function.

It is still using direct SOQL querying to achieve what it does. I wasn't able to get it working with a "copied" model, after an array of about 140 added conditions (aggregate query special conditioning to get next row) the model.load SOQL query started inserting garbage characters in between the condition logic on the SOQL query and the SOQL query failed / was invalid.

Also, the modelLoader function I put together only seems to work if your model has "Process model client side" checked. I'm not sure why but it will throw back that your SOQL query is invalid if your model isn't processing client side. I've checked the SOQL query it gives back saying it's invalid and the query appears to be valid, I even ran the query myself and it works, so I'm not sure what's going on with that yet.

Here's the updated code, if you do have a chance to take a look let me know if you have any pointers for things I might be missing or ways to fix it not working unless the model is processed client side.

Thanks!

skuid.model['modelLoader'] = function(model,fparams){
	var deferred = $.Deferred();
	if(model===undefined){
		deferred.reject('Model undefined');
		return deferred.promise();
	}

	var fparams = fparams || {};

	var limit = fparams['limit'] || model['recordsLimit'] || 200;
	var exportWhenDone = fparams['exportWhenDone'] || false;
	var exportOptions = fparams['exportOptions'] || {};
	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,
		exportWhenDone: exportWhenDone,
		exportOptions: exportOptions
	});

	//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 tempRows = fparams['tempRows'] || [];
		var progressCallback = fparams['progressCallback'] || undefined;
		var promiseResolve = fparams['promiseResolve'] || undefined;
		var promiseReject = fparams['promiseReject'] || undefined;
		var exportWhenDone = fparams['exportWhenDone'] || false;
		var exportOptions = fparams['exportOptions'] || {};

		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 = '';
		var orderby = '';
		var having = '';
		var groupby = '';
		var groupbyItemsArr = [];

		let fieldsStr = '';

		//construct fields from model fields
		if(model.fields !== undefined){
			for(let i=0;i<model.fields.length;i++){
				let f = model.fields[i];

				//skip UI only fields
				if(f.uiOnly === true){
					continue;
				}

				let fieldBefore = '';
				let fieldAfter = '';
				let field = f.id;
				let fieldName = f.name || '';
				if(fieldName != ''){
					fieldName = ' '+fieldName;	
				}
				//remove name for non-aggregate models
				if(model.isAggregate === false){
					fieldName = '';
				}

				if(fieldsStr != ''){
					fieldsStr += ',';
				}

				if(f.function !== undefined){
					fieldBefore = f.function+'(';
					fieldAfter = ')';
				}

				fieldsStr += `${fieldBefore}${field}${fieldAfter}${fieldName}`;

				if(model.isAggregate===true && f.groupable === true){
					if(groupby != ''){
						groupby += ',';
					}
					groupby += field;
					groupbyItemsArr.push(field);
				}
			}
		}

		//Get our conditions
		let ourConditions = parseConditionsSOQL(model.conditions);
		//abort query on undefined
		if(ourConditions === undefined){
			promiseResolve({aborted: true});
			return;
		}
		let ourConditionLogic = parseConditionLogic(model.conditions, model.conditionLogic);
		if(ourConditionLogic === undefined){
			promiseResolve({aborted: true});
			return;
		}

		let ourConditionStrPre = '';
		let ourConditionStr = '';

		//iterate the returned condition logic
		for(let i=0;i<ourConditionLogic.length;i++){
			let cl = ourConditionLogic[i];

			//if the item is not a number, add it to our string
			if(isNaN(cl)){
				ourConditionStr += cl;
			}
			//if the item is a number, get it out of ourConditions and add it
			else{
				ourConditionStr += ourConditions[cl];
			}
		}

		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 = [];

				//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);
					}
				}

				//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 += ')';
				}
			}
			
		}

		let lastRowConditionsPre = '';

		//If we have Last Row conditions, need to add AND if there is a WHERE
		if(lastRowConditions != '' && ourConditionStr != ''){
			lastRowConditionsPre = ' AND ';
		}

		if(ourConditionStr != '' || lastRowConditions != ''){
			ourConditionStrPre = ' WHERE ';
		}

		let groupbyPre = '';

		if(groupby != ''){
			groupbyPre = ' GROUP BY ';
		}

		var queryStr = 'SELECT ' + fieldsStr + ' FROM ' + model.objectName + ourConditionStrPre + ourConditionStr + lastRowConditionsPre + lastRowConditions + groupbyPre + 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 tempModel
				tempRows = tempRows.concat(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,
					tempRows: tempRows,
					exportWhenDone: exportWhenDone,
					exportOptions: exportOptions
				});
			}
			//Otherwise we've finished querying, resolve
			else{
				//sort our tempRows based on original sorting parameters set in the model
				tempRows = sortRows(tempRows,model);

				//adopt our tempRows into our model
				model.adoptRows(tempRows);

				if(exportWhenDone){
					
					skuid.$.when(model.exportData(exportOptions)).done(function(result) {
						promiseResolve();
					}).fail(function(result){
						promiseReject(result.error);
					});
					
				}
				else{
					promiseResolve();
				}
				
				
			}
		}).fail(function(queryResult) {
			
			promiseReject(queryResult.error);

		});

		function getAggFieldIdFromName(model,name){
			if(model.isAggregate === false){
				return 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 name;
		}

		function getAggFieldNameFromId(model,id){
			if(model.isAggregate === false){
				return id;
			}

			for(let i=0;i<model.fields.length;i++){
				if(model.fields[i]['function']===undefined && model.fields[i]['id'] == id){
					return model.fields[i]['name'];
				}
			}

			return id;
		}

		function operatorConvert(operator){
			if(operator == 'gt'){
				return '>';
			}
			else if(operator == 'gte'){
				return '>=';
			}
			else if(operator == 'lt'){
				return '<';
			}
			else if(operator == 'lte'){
				return '<=';
			}

			return operator;
		}

		//Pass an array of conditions
		//returns an array of the conditions translated into SOQL format
		//if no conditions exist, returns empty array
		//inactivated conditions will be undefined in the array
		//if we need to abort the query, returns undefined
		function parseConditionsSOQL(conditions){
			if(conditions === undefined || conditions.length == 0){
				return [];
			}

			let conditionsItems = [];

			//loop through conditions
			for(let i=0;i<conditions.length;i++){
				let c = conditions[i];

				//only pick up active conditions
				//if a condition is inactive push an element to the array to preserve condition ordering
				if(c.inactive === true){
					conditionsItems.push(undefined);
				}
				else{
					//field from another model
					if(c.type == 'modelmerge'){
						let thisField = c.field;
						let mergeField = c.mergeField;
						let operator = operatorConvert(c.operator);
						let noValueBehavior = c.noValueBehavior;
						let mergeModel = skuid.model.getModel(c.model);
						let mergeModelFirstRow = mergeModel.getFirstRow();
						//If we have no row
						if(mergeModelFirstRow === undefined || mergeModelFirstRow === null){
							//if noquery specified, abort query
							if(noValueBehavior == 'noquery'){
								return undefined;
							}
							//otherwise skip this condition
							else{
								conditionsItems.push(undefined);
							}
						}
						let mergeModelFirstRowValue = mergeModel.getFieldValue(mergeModelFirstRow,mergeField,true);
						if(mergeModelFirstRowValue === undefined || mergeModelFirstRowValue === null){
							mergeModelFirstRowValue = 'NULL';
						}
						let mergeModelData = mergeModel.data;
						let encloseValueInQuotes = c.encloseValueInQuotes;
						let precede = '';
						let valueBefore = '';
						let valueAfter = '';
						let value = '';
						let thisItem = '';
						
						if(operator.includes('start')){
							operator = 'LIKE';
							valueAfter = '%';
						}
						else if(operator.includes('end')){
							operator = 'LIKE';
							valueBefore = '%';
						}
						else if(operator.includes('contain')){
							operator = 'LIKE';
							valueBefore = '%';
							valueAfter = '%';
						}
						
						if(operator.includes('does not')){
							precede = 'NOT ';
						}

						//if in or not in, we need to iterate over the other model and include / exclude all those values
						if(operator == 'in' || operator == 'not in'){
							valueBefore = '(';
							valueAfter = ')';
							for(let j=0;j<mergeModelData.length;j++){
								let mr = mergeModelData[j];
								let mf = mergeModel.getFieldValue(mr,mergeField,true);

								if(mf === undefined || mf === null){
									mf = 'NULL';
								}

								if(value != ''){
									value+=',';
								}

								if(mf != 'NULL' && encloseValueInQuotes){
									value += `'${mf}'`;
								}
								else{
									value += mf;
								}
							}
						}
						else if(mergeModelFirstRow !== undefined && mergeModelFirstRow !== null){
							if(mergeModelFirstRowValue != 'NULL' && encloseValueInQuotes){
								value = `'${mergeModelFirstRowValue}'`;
							}
							else{
								value = mergeModelFirstRowValue;
							}
						}

						//LIKE NULL set operator to = instead
						if(operator == 'LIKE' && value == 'NULL'){
							operator = '=';
						}
						//IN nothing set value to NULL instead
						if((operator == 'in' || operator == 'not in') && value == ''){
							value = 'NULL';
						}
						
						conditionsItems.push(`(${precede}${thisField} ${operator} ${valueBefore}${value}${valueAfter})`);
					}
					//multiple specified values
					else if(c.type == 'multiple'){
						//Field in (
						//Field not in (
						let thisItem = `${c.field} ${c.operator} (`;

						if(c.values === undefined || c.values.length == 0){
							thisItem += 'NULL';
						}
						else{
							for(let j=0;j<c.values.length;j++){
								let v = c.values[j];

								if(j!=0){
									thisItem += `,`;
								}

								if(c.encloseValueInQuotes===true){
									thisItem += `'${v}'`;
								}
								else{
									thisItem += `${v}`;
								}
							}
						}

						thisItem += `)`;

						conditionsItems.push(thisItem);
					}
					//subquery
					else if(c.type == 'join'){
						let field = c.field;
						let subConditionLogic = c.subConditionLogic;
						let encloseValueInQuotes = c.encloseValueInQuotes;
						let operator = operatorConvert(c.operator);
						let fieldTargetObjects = c.fieldTargetObjects;
						let joinField = c.joinField;
						let joinObject = c.joinObject;
						//array of subconditions
						let subConditions = c.subConditions;
						//construct subcondition logic in string form
						let subConditionsParsed;
						let subConditionLogicParsed;

						let thisItem = `(${field} ${operator} (SELECT ${joinField} FROM ${joinObject}`;

						if(subConditions !== undefined && subConditions.length > 0){
							thisItem += ` WHERE `;
							
							subConditionLogicParsed = parseConditionLogic(subConditions,subConditionLogic);
							//Abort if undefined
							if(subConditionLogicParsed === undefined){
								return undefined;
							}
							subConditionsParsed = parseConditionsSOQL(subConditions);
							//Abort if undefined
							if(subConditionsParsed === undefined){
								return undefined;
							}

							for(let j=0;j<subConditionLogicParsed.length;j++){
								let scl = subConditionLogicParsed[j];
								
								//If not a number, add to our thisItem string
								if(isNaN(scl)){
									thisItem += scl;
								}
								//otherwise get from our subConditionsParsed array
								else{
									thisItem += subConditionsParsed[scl];
								}
							}
						}
						
						thisItem += `))`;

						conditionsItems.push(thisItem);
					}
					//standard fieldvalue, userinfo, datasourceuserinfo
					else{
						let value = c.value;
						let operator = operatorConvert(c.operator);
						let noValueBehavior = c.noValueBehavior;

						if(noValueBehavior !== undefined && (value === undefined || value == '')){
							if(noValueBehavior == 'deactivate'){
								conditionsItems.push(undefined);
								continue;
							}
							else if(noValueBehavior == 'noquery'){
								return undefined;
							}
						}

						if(value === undefined || value === null){
							value = 'NULL';
						}

						let precede = '';
						
						if(operator.includes('start')){
							operator = 'LIKE';
							value = value+'%';
						}
						else if(operator.includes('end')){
							operator = 'LIKE';
							value = '%'+value;
						}
						else if(operator.includes('contain')){
							operator = 'LIKE';
							value = '%'+value+'%';
						}
						
						if (operator.includes('does not')){
							precede = 'NOT ';
						}
						
						if(value != 'NULL' && c.encloseValueInQuotes){
							value = `'${value}'`;
						}
						
						//LIKE NULL is invalid, switch operator to equals
						if(operator == 'LIKE' && value == 'NULL'){
							operator = '=';
						}
						
						conditionsItems.push(`(${precede}${c.field} ${operator} ${value})`);
						
					}
					
				}
			}

			return conditionsItems;
		}

		function sortRows(rows,model){
			if(model === undefined){
				return rows;
			}

			var orderby = model.orderByClause;
			if(orderby === undefined || orderby === null || orderby == ''){
				return rows;
			}

			//turn orderby into an array of objects
			var orderbyArr = [];

			let orderbySplit = orderby.split(',');

			for(let i=0;i<orderbySplit.length;i++){
				let os = orderbySplit[i].trim();
				let field;
				let ascDesc;

				if(os.toUpperCase().endsWith('ASC')){
					ascDesc = 'ASC';
					field = os.substring(0, os.length - 3).trim();
				}
				else if(os.toUpperCase().endsWith('DESC')){
					ascDesc = 'DESC';

					field = os.substring(0, os.length - 4).trim();
				}
				else{
					field = os;
					ascDesc = 'ASC';
				}

				if(model.isAggregate === true){
					field = getAggFieldNameFromId(model,field);
				}

				orderbyArr.push({field: field, ascDesc: ascDesc});
			}



			rows.sort(function(a, b){
				for(let i=0;i<orderbyArr.length;i++){
					let ob = orderbyArr[i];
					let field = ob.field;
					let ascDesc = ob.ascDesc;

					let aField = Object.byString(a,field);
					let bField = Object.byString(b,field);
					
					if(aField < bField){
						if(ascDesc == 'ASC'){
							return -1;
						}
						else if(ascDesc == 'DESC'){
							return 1;
						}
					}
					else if(aField > bField){
						if(ascDesc == 'ASC'){
							return 1;
						}
						else if(ascDesc == 'DESC'){
							return -1;
						}
					}
					
				}

				return 0;
			});

			return rows;
		}

		Object.byString = function(o, s) {
			s = s.replace(/\[(\w+)\]/g, '.$1'); // convert indexes to properties
			s = s.replace(/^\./, '');           // strip a leading dot
			var a = s.split('.');
			for (var i = 0, n = a.length; i < n; ++i) {
				var k = a[i];
				if (k in o) {
				o = o[k];
				}
				else{
					return;
				}
			}
			return o;
		}

		//Takes an array of conditions and a string of condition logic
		//Returns an array of strings and numbers
		//Strings are SOQL condition logic to directly embed (eg. AND OR ())
		//Numbers are array positions in the conditions array of those conditions
		//If undefined is returned, abort query
		function parseConditionLogic(conditions,conditionLogic) {
			if (conditionLogic === undefined || conditionLogic == '') {
				return [];
			}

			//always surround with parenthesis
			conditionLogic = '(' + conditionLogic + ')';
			

			let match = conditionLogic.match(/( AND )|([0-9]+)|([\(\)])|( OR )/g);

			let ourConditions = parseConditionsSOQL(conditions);

			//abort query if undefined
			if(ourConditions === undefined){
				return undefined;
			}
			
			match = deactivateLogic({match: match, conditions: conditions, ourConditions: ourConditions});

			for(let i=0;i<match.length;i++){
				if(!isNaN(match[i])){
					//fix to numerical index of condition itself
					match[i] = (Number(match[i])-1);
				}
			}
			
			//recursive function to deactivate logic
			function deactivateLogic(fparams){
				fparams = fparams || {};
				let match = fparams.match || undefined;
				let index = fparams.index|| undefined;
				let conditions = fparams.conditions|| undefined;
				let deactivate = fparams.deactivate || false;
				let ourConditions = fparams.ourConditions || false;
				let m;

				//index is undefined, this is the base function call outside of recursion
				if(index === undefined){
					index = 0;
				}
				//we got to the end of iteration, return match
				else if(match.length <= index){
					return match;
				}

				m = match[index];
				
				//we're not in deactivation mode
				if(!deactivate){
					//If this is a number
					if(!isNaN(m)){
						let n = Number(m)-1;
						//if this condition is inactive or has been deactivated in ourConditions due to missing rows, recurse to deactivate logic
						if(conditions[n].inactive === true || ourConditions[n] === undefined){
							//remove the item
							match.splice(index,1);
							//run over our match array fixing validity issues
							match = deactivateLogic({match: match, conditions: conditions, ourConditions: ourConditions,  deactivate: true});
							//rerun from scratch
							match = deactivateLogic({match: match, conditions: conditions, ourConditions: ourConditions});
						}
						//otherwise it's valid let's iterate recursively
						else if(index+1 < match.length){
							match = deactivateLogic({match: match, index: index+1, conditions: conditions, ourConditions: ourConditions});
						}
					}
					//if this is not a number, iterate recursively
					else if(index+1 < match.length){
						match = deactivateLogic({match: match, index: index+1, conditions: conditions, ourConditions: ourConditions});
					}
				}
				//we're in deactivation mode, check match array for validity
				else{
					let lastItem;
					let lastIndex;
					if(index != 0){
						lastIndex = index-1;
						lastItem = match[lastIndex];
					}
					let nextItem;
					let nextIndex;
					if(index != match.length-1){
						nextIndex = index+1;
						nextItem = match[nextIndex];
					}

					//get rid of touching AND/OR items or AND/OR that are preceded by '(' or have ')' after them
					if(m == ' AND ' || m == ' OR '){
						if(lastItem === undefined){
							//Remove this AND/OR
							match.splice(index,1);
							//rerun deactivation check from scratch
							match = deactivateLogic({match: match, conditions: conditions, ourConditions: ourConditions, deactivate: true});
						}
						else if(nextItem == ' AND ' || nextItem == ' OR ' || nextItem == ')'){
							//Remove this AND/OR
							match.splice(index,1);
							//rerun deactivation check from scratch
							match = deactivateLogic({match: match, conditions: conditions, ourConditions: ourConditions, deactivate: true});
						}
						else if(lastItem == ' AND ' || lastItem == ' OR ' || lastItem == '('){
							//Remove this AND/OR
							match.splice(index,1);
							//rerun deactivation check from scratch
							match = deactivateLogic({match: match, conditions: conditions, deactivate: true});
						}
						//nothing broken here, proceed to next index
						else if(index+1 < match.length){
							match = deactivateLogic({match: match, index: index+1, conditions: conditions, ourConditions: ourConditions, deactivate: true})
						}
					}
					//fix broken parenthesis
					else if(m == '('){
						if(nextItem == ')'){
							match.splice(index,2);
							//rerun deactivation check from scratch
							match = deactivateLogic({match: match, conditions: conditions, ourConditions: ourConditions, deactivate: true});
						}
						//nothing broken here, proceed to next index
						else if(index+1 < match.length){
							match = deactivateLogic({match: match, index: index+1, conditions: conditions, ourConditions: ourConditions, deactivate: true})
						}
					}
					else if(m == ')'){
						if(lastItem===undefined){
							//Remove this )
							match.splice(index,1);
							//rerun deactivation check from scratch
							match = deactivateLogic({match: match, conditions: conditions, ourConditions: ourConditions, deactivate: true});
						}
						else if(lastItem == '('){
							match.splice(lastIndex,2);
							//rerun deactivation check from scratch
							match = deactivateLogic({match: match, conditions: conditions, ourConditions: ourConditions, deactivate: true});
						}
						//nothing broken here, proceed to next index
						else if(index+1 < match.length){
							match = deactivateLogic({match: match, index: index+1, conditions: conditions, ourConditions: ourConditions, deactivate: true})
						}
					}
					//nothing broken here, proceed to next index
					else if(index+1 < match.length){
						match = deactivateLogic({match: match, index: index+1, conditions: conditions, ourConditions: ourConditions, deactivate: true})
					}
				}
				
				return match;
			}

			return match;
		}
	}

	return deferred.promise();
}


Photo of Zach McElrath

Zach McElrath, Employee

  • 56,134 Points 50k badge 2x thumb
For anyone who comes to this post later, it looks like I was going off of old information when I voiced a concern about the security of using skuid.sfdc.api.query(), which makes calls to the Salesforce REST API --- in our testing, the Salesforce REST API enforces object and field-level security correctly, no matter what query you make. For example, if you try to query on a field that you don't have access to, the query will throw an error.