Paginated Glide Record + Expanded GR Method Support

[JosephW1]

Hello,

(There are existing paginated GR solutions out there. The reason I made this one is to add the seemingly missing support for the bulk of the most commonly used GR methods and page URLs. Knowing me, I overlooked a solution that already offered all of this, but the adventure and learning experience hopefully will make my effort still worth it. Please advise if there is a better solution out there! I'll link to it here, if so. Thanks.

I've added support for most of the GlideRecord methods: addQuery (and its addOrCondition callback), addEncodedQuery, addActiveQuery, addNotNullQuery, addNullQuery, addJoinQuery (all 3 variants and their addConditions callback), orderBy, orderByDesc, and setLimit. I may add more in the future, but those should be the heavy-hitters. Right?)

INTRODUCTION

Have you ever had a need to page through a GlideRecord? For example, say A) your query has nearly 70,000 rows, B) you want to break it up into 30,000 row queries, and C) you want to do this programmatically? The traditional manual way has a lot of little steps I won't list out here for the sake of space. But, back to the point, how do we that? By breaking the query up in the script. How do you break it up in a script without pagination?

If you're like me, that would probably be where you learn about GlideRecord.chooseWindow. Which is great! This article's script include is wholly built around it, actually; it can definitely handle this task for you. However, using it to traverse multiple pages requires self-tracking quite a few variables:

1. How big do you want your page count to be?
2. How many rows does my overall query have?
3. How many pages does my overall query have?
4. What page am I currently in?
5. What is the start row and end row of my current page?
6. How many rows into my query am I?
7. Are there any pages remaining?

Now, while these questions aren't very hard to figure out - you can probably accomplish all of that in less than 30 lines of code - it's definitely not as easy as sitting back and doing something like this:

var pgr = new PaginatedGlideRecord('incident'); //hypothetical, does not exist (YET!)
pgr.setPageSize(20);
pgr.query();
while (pgr.nextPage()) {
	//do stuff with page
	while (pgr.nextRow()) {
		//do stuff with row
	}
}

See? That hypothetical PaginatedGlideRecord is doing all that work behind the scenes. But hey, guess what? It can be that easy! Easily, even!

LET'S MAKE IT HAPPEN

Here is a script include that fuels the above code template.

Name:	PaginatedGlideRecord		Application:	Global
Client callable:	global.PaginatedGlideRecord		Active:	TRUE
Description:	A utility for iterating large GlideRecord lists in a paginated format. Designed to support many of the common GR methods such as: addQuery, addNotNullQuery, orderBy, addJoinQuery, and more! NOTE: Much inspiration was taken from SNProTips' https://snprotips.com/blog/2016/9/1/gliderecord-pagination-page-through-records-with-ease. Thanks!

var PaginatedGlideRecord = Class.create();
PaginatedGlideRecord.prototype = {
	initialize: function(tableName) {
		if (!tableName) {
			gs.logError(this.type + ' initialized without a tableName.');
			throw this.type + ' initialized without a tableName.';
		}

		this.tableName      = tableName;
		this.sortFields     = [];
		this.encodedQueries = [];
		this.queries        = [];
		this.joinQueries    = [];
		this.activeQueries  = {};
		this.notNullFields  = {};
		this.nullFields     = {};
		this.rowLimit       = 10000; //default
		this.pageSize       = 20; //default
		this.currentPage    = 0;
		this.currentRow     = 0;
	},

	addQuery: function(field, operator, value) {
		if (!operator) { // Encoded Query Scenario     Ex: pgr.addQuery(encodedQuery)
			return this.encodedQueries.push(field);
		} // Ordinary Scenario     Ex: pgr.addQuery(field, value) OR pgr.addQuery(field, operator, value)
		this.queries.push({field: field, operator: value ? operator : '=', value: value ? value : operator, subConds: []});
		return this._subCondCallback(this.queries[this.queries.length -1]);
	},

	addEncodedQuery: function(encodedQuery) {
		this.encodedQueries.push(encodedQuery);
	},

	addActiveQuery: function(fieldName) {
		this.activeQueries[fieldName || 'active'] = true;
	},

	addNotNullQuery: function(fieldName) {
		this.notNullFields[fieldName] = true;
	},

	addNullQuery: function(fieldName) {
		this.nullFields[fieldName] = true;
	},

	addJoinQuery: function(joinTable, primaryField, joinTableField) {
		joinTableField = joinTableField || this._getJoinTableField(joinTable); //self-resolve omitted joinTableField
		this.joinQueries.push({joinTable: joinTable, primaryField: primaryField || 'sys_id', joinTableField: joinTableField, subConds: []});
		
		return this._subCondCallback(this.joinQueries[this.joinQueries.length - 1]);
	},

	orderBy: function(fieldName) {
		this._addSortField(fieldName, 'orderBy');
	},

	orderByDesc: function(fieldName) {
		this._addSortField(fieldName, 'orderByDesc');
	},

	setLimit: function(limit) {
		this.rowLimit = parseInt(limit) || this.rowLimit;
	},

	setPageSize: function(pageSize) {
		this.pageSize = parseInt(pageSize) || this.pageSize;
	},

	query: function() { //used to collect the main query and its rowCount
		if (this.sortFields.length == 0) {
			this.orderBy('sys_created_on'); //default sort in lieu of client-added sort, required for URL building
		}
		this.rowCount    = this._getMainGaAndRowCount();
		this.pageCount   = Math.ceil(this.rowCount / this.pageSize);
		this.hasNextPage = this.pageCount > 0;
	},

	nextPage: function() {
		if (!this.hasNextPage) {
			return false;
		} else {
			this.currentPage++;
			this.currentRow   = (this.currentPage - 1) * this.pageSize;
			this.pageFirstRow = this.currentRow;
			this.pageLastRow  = Math.min(this.currentRow + this.pageSize, this.rowLimit, this.rowCount);
			this.hasPrevPage  = this.currentPage > 1; //can be used for back button flagging
			this.hasNextPage  = this.currentPage < this.pageCount; //can be used for forward button flagging

			this.gr = new GlideRecord(this.tableName);
			this._applyConfigToGlideObject(this.gr);
			this.gr.chooseWindow(this.currentRow, this.pageLastRow);
			this.gr.query();

			this._getPageFirstAndLastRow();

			return true;
		}
	},

	nextRow: function() {
		this.currentRow++;
		return this.gr.next();
	},

	setPage: function(pageNumber) {
		this.currentPage = Math.max(Math.min(parseInt(pageNumber), this.pageCount), 1) - 1;
		this.hasNextPage = this.currentPage < this.pageCount;
		this.nextPage();
	},

	loadFirstPage: function() {
		this.setPage(1);
	},

	loadLastPage: function() {
		this.setPage(this.pageCount);
	},
	
	getMainUrl: function() {
		return this._getUrl(true);
	},

	getPageUrl: function() {
		return this._getUrl(false);
	},

	type: 'PaginatedGlideRecord'
};

Object.defineProperties(PaginatedGlideRecord.prototype, { //define non-enumerable quasi-private functions
	_applyConfigToGlideObject: {value: function(GlideObject) { //used to apply methods to GA & GR
		this.queries.forEach(function(query) {
			var cond = GlideObject.addQuery(query.field, query.operator, query.value);
			query.subConds.forEach(function(subCond) {
				cond[subCond.method](subCond.field, subCond.operator, subCond.value);
			});
		});
		this.encodedQueries.forEach(function(encodedQuery) {
			GlideObject.addEncodedQuery(encodedQuery);
		});
		for (var activeField in this.activeQueries) {
			GlideObject.addActiveQuery(activeField);
		}
		for (var notNullField in this.notNullFields) {
			GlideObject.addNotNullQuery(notNullField);
		}
		for (var nullField in this.nullFields) {
			GlideObject.addNullQuery(nullField);
		}
		this.joinQueries.forEach(function(join) { //apply in encodedQuery form for later compatibility with gr.getEncodedQuery
			var data = {joinConfig: join.primaryField + ',' + join.joinTableField + ',' + join.joinTable, query: []};
			join.subConds.forEach(function(cond) {
				data.query.push((cond.method == 'addCondition' ? '^' : '^OR') + cond.field + cond.operator + cond.value);
			});
			GlideObject.addEncodedQuery('SUBQUERY' + data.joinConfig + (data.query.join('') || '^sys_idANYTHING') + '^ENDSUBQUERY');
		});
		GlideObject.setLimit(this.rowLimit);
		if (GlideObject.constructor.name == 'GlideAggregate') {return;} //stop before sorting main GA (ga.setLimit issue)
		this.sortFields.forEach(function(sortData) {
			GlideObject[sortData.method](sortData.fieldName);
		});
	}},

	_getMainGaAndRowCount: {value: function() { //used to get the main query's rowCount. For public access use pgr.rowCount.
		this.mainGa = new GlideAggregate(this.tableName); //GA speedhack. It supports all the GR methods we are using.
		this._applyConfigToGlideObject(this.mainGa);
		this.mainGa.query();
		return this.mainGa.getRowCount();
	}},

	_addSortField: {value: function(fieldName, method) { //used to prevent sort fields from duplicating across-methods					
		var updated = this.sortFields.some(function(e) {
			if (e.fieldName == fieldName) {
				e.method = method;
				return true; //array.some stops at first true returned (efficient)
			}
		});
		if (!updated) {
			this.sortFields.push({fieldName: fieldName, method: method});
		}
	}},

	_subCondCallback: {value: function (entry) {
		var callback = {};
		function _applySubQuery(method, field, operator, value) {
			if (!operator) {
				gs.logError(this.type + ': Must provide at least 2 arguments to ' + method);
				throw this.type + ': Must provide at least 2 arguments to ' + method;
			}
			entry.subConds.push({field: field, operator: value ? operator : '=', value: value ? value : operator, method: method});
			return callback; //allow chaining callbacks
		}
		callback.addCondition = function(field, operator, value) {
			return _applySubQuery('addCondition', field, operator, value);
		};
		callback.addOrCondition = function(field, operator, value) {
			return _applySubQuery('addOrCondition', field, operator, value);
		};
		return callback; //provide callback
	}},

	_getJoinTableField: {value: function(joinTable) { //self-resolve omitted addJoinQuery args for more informed URL building
		var tableHierarchy = new TableUtils(this.tableName).getTables(); //ordered upstream tiers of primaryTable's hierarchy
		var fieldsGr = new GlideRecord(joinTable); //we need one row from this joinTable's GlideRecord
		var fields = fieldsGr.initialize() || fieldsGr.getFields(); //initialize gets a blank row and returns null
		var minTier = {}; //the lowest tier of the primaryTable's hierarchy that is referenced anywhere in the joinTable
		for (var i = 0; i < fields.size() && minTier.i != 0; i++) { //loop join fields til a self-reference (0) is found
			var ED = fields.get(i).getED(); //ElementDescriptor: holds information about the current field
			var i2 = tableHierarchy.indexOf(ED.getReference()); //find which hierarchy tier the current field references
			minTier = i2 == -1 || i2 >= minTier.i || !ED.isReference() ? minTier : {i: i2, refField: fields.get(i).getName()};
		}
		if (!minTier.refField) {
			gs.logError(this.type + ': Found no link between ' + this.tableName + ' and ' + joinTable);
			throw this.type + ': Found no link between ' + this.tableName + ' and ' + joinTable;
		}
		return minTier.refField;
	}},

	_getPageFirstAndLastRow: {value: function() { //preparation step for building page URLs
		this.gr.setLocation(0);
		this.pageFirstGr = new GlideRecord(this.tableName);
		this.pageFirstGr.get(this.gr.getUniqueValue());

		this.gr.setLocation(this.pageLastRow - this.pageFirstRow - 1);
		this.pageLastGr = new GlideRecord(this.tableName);
		this.pageLastGr.get(this.gr.getUniqueValue());

		this.gr.setLocation(-1);
	}},
	
	_getUrl: {value: function(isMainUrl) {
		// CAUTION: These page URLs could be slightly inaccurate when all of the ordered fields have exactly the same value
		// across multiple records that occur on both sides of a page's border. Use these page URLs at your own risk!
		// FYI: Join Queries - aka subqueries - render out in an unrecognizable (blank) way in list-view condition builders.
		var baseUrl      = gs.getProperty('glide.servlet.uri');
		var listUrl      = 'nav_to.do?uri=/' + this.tableName + '_list.do?sysparm_query=';
		var encodedQuery = this[isMainUrl ? 'mainGa' : 'gr'].getEncodedQuery();
		var operatorMap  = [{orderBy:'>=', orderByDesc:'<='}, {orderBy:'<=', orderByDesc:'>='}];
		var queryParts   = [];
		this.sortFields.forEach(function(sortData) {if(!isMainUrl) { //skip this step if building a mainUrl
			queryParts.push(sortData.fieldName + operatorMap[0][sortData.method] + this.pageFirstGr[sortData.fieldName]);
			queryParts.push(sortData.fieldName + operatorMap[1][sortData.method] + this.pageLastGr[sortData.fieldName]);
		}}, this);
		return baseUrl + listUrl + queryParts.join('^') + '^' + encodedQuery;
	}}
});

REVISITED EXAMPLE

So, once you get done checking our script out, let's get back to our example.

"For example, say A) your query has nearly 70,000 rows, B) you want to break it up into 30,000 row queries, and C) you want to do this programmatically?"

It's as easy as this!

gs.log('DEMO 1: STANDARD PAGED QUERY + PAGE URLS', 'PGR Page Link Demo');
var pgr = new PaginatedGlideRecord('cmdb_ci_computer');
pgr.setPageSize(30000);
pgr.setLimit(70000);
pgr.query();
while (pgr.nextPage()) {
	gs.log('⠀⠀Page ' + pgr.currentPage + ': ' + pgr.currentRow + '-' + pgr.pageLastRow, 'PGR Page Link Demo');
	gs.log('⠀⠀⠀⠀URL: ' + pgr.getPageUrl(), 'PGR Page Link Demo');
}

Which would put messages similar to these into your logs. These provide you with the 30,000 rows batched queries that you wanted in a fraction of the time it would take you to build them yourself manually. Enjoy your copy and paste! (CAUTION: These page URLs could be slightly inaccurate when all of the ordered fields have exactly the same value across multiple records that occur on both sides of a page's border. Use these page URLs at your own risk!)

Message	Source
DEMO 1: STANDARD PAGED QUERY + PAGE URLS	PGR Page Link Demo
⠀⠀Page 1: 0-30000	PGR Page Link Demo
⠀⠀⠀⠀URL: https://yourbaseurl.servicenowservices.com/nav_to.do?uri=/cmdb_ci_computer_list.do?sysparm_query=sys_created_on>=2017-12-18 04:33:31^sys_created_on<=2018-08-06 21:24:14^ORDERBYsys_created_on	PGR Page Link Demo
⠀⠀Page 2: 30000-60000	PGR Page Link Demo
⠀⠀⠀⠀URL: https://yourbaseurl.servicenowservices.com/nav_to.do?uri=/cmdb_ci_computer_list.do?sysparm_query=sys_created_on>=2018-08-06 21:28:59^sys_created_on<=2018-10-22 17:20:36^ORDERBYsys_created_on	PGR Page Link Demo
⠀⠀Page 3: 60000-69265	PGR Page Link Demo
⠀⠀⠀⠀URL: https://yourbaseurl.servicenowservices.com/nav_to.do?uri=/cmdb_ci_computer_list.do?sysparm_query=sys_created_on>=2018-10-22 17:20:37^sys_created_on<=2018-11-16 12:04:34^ORDERBYsys_created_on	PGR Page Link Demo

ADDITIONAL EXAMPLE

Here is one more rudimentary demo snippet to A) show support for some of the more advanced GR methods, B) show support for setting the page on-demand, and C) help illustrate how to utilize this script include in a paginated UI.

gs.log('DEMO 2: ADVANCED GR METHODS SUPPORT + SET PAGE + UI FLAGs');
gs.log(' NOTE: These flags would be used for enabling/diabling UI prev/next buttons.');
var pgr = new PaginatedGlideRecord('cmdb_ci_computer');
pgr.setPageSize(5000);
pgr.setLimit(80000);
pgr.addQuery('form_factor', 'Laptop').addOrCondition('form_factor', 'NULL');
var join = pgr.addJoinQuery('incident', 'sys_id', 'cmdb_ci');
join.addCondition('state', '6').addOrCondition('state', '7');
pgr.addEncodedQuery('nameISNOTEMPTY');
pgr.addNotNullQuery('serial_number'); //these conditions are pointless other than for showing that pgr supports them
pgr.orderBy('sys_created_by');
pgr.orderByDesc('sys_created_on');
pgr.query();
gs.log('Main: 0-' + pgr.rowCount); //use this to build total record count UI element
while (pgr.nextPage()) {
	var flags = ' (PREV: ' + pgr.hasPrevPage + ' / NEXT: ' + pgr.hasNextPage + ')';
	gs.log('⠀⠀Page ' + pgr.currentPage + ': ' + pgr.currentRow + '-' + pgr.pageLastRow + flags);
	while (pgr.nextRow()) {
		//do stuff? Iterating the page's rows is totally doable if you need to!
	}
}
pgr.setPage(2); //yes, we can traverse to any page on-demand
var flags2 = ' (PREV: ' + pgr.hasPrevPage + ' / NEXT: ' + pgr.hasNextPage + ')';
gs.log('Returned to Page ' + pgr.currentPage + ': ' + pgr.currentRow + '-' + pgr.pageLastRow + flags2, 'PGR Demo 2');

Which puts these messages into your logs: (However, in this case all you care about is integrating pgr.rowCount, pgr.hasPrevPage, and pgr.hasNextPage into your UI, not the logs; they're just shown here for the readers.)

Message	Source
DEMO 2: ADVANCED QUERY SUPPORT (OR CONDITION + JOINED QUERY) + PAGE URLS	PGR Demo 2
NOTE: These flags would be used for enabling/disabling UI prev/next buttons.	PGR Demo 2
Main: 0-18892	PGR Demo 2
⠀⠀Page 1: 0-5000 (PREV: false / NEXT: true)	PGR Demo 2
⠀⠀Page 2: 5000-10000 (PREV: true / NEXT: true)	PGR Demo 2
⠀⠀Page 3: 10000-15000 (PREV: true / NEXT: true)	PGR Demo 2
⠀⠀Page 4: 15000-18892 (PREV: true / NEXT: false)	PGR Demo 2
Returned to Page 2: 5000-10000 (PREV: true / NEXT: true)	PGR Demo 2

CONCLUSION

With this script include, implementing paginated GlideRecords, with support for all of your usual GlideRecord methods - addQuery, orderBy, addNotNullQuery, etc - is nearly effortless! As shown in these two examples, this can be used to build paginated UIs that require support for these GR methods and even for obtaining URLs for each page.

I hope that this is useful to some. Please advise on any changes/improvements. Thanks! Have a great day!

NOTEWORTHY

SN Pro Tips has provided a "PagedGlideRecord". You can find it here, on their website. Much of my inspiration and a bit of my code was taken from their post, so I want to mention them here! Thank you very much, SN Pro Tips!

Kind Regards,

Joseph