GlideQuery() Demystified

I have been hearing a lot about GlideQuery() lately, so after spending some time learning what it is about, I am sharing the below information hoping it would help.

• Server API

• Two script includes are being used for this: GlideQuery, GlideQueryEvaluator (protected, not exposed in the instance)

• GQ mostly seems to use GlideRecord inside the GlideQueryEvaluator script

• Can enhance the GlideRecord() experience by adding additional layers on it (we will discuss these layers below) and possibly replace GlideAggregate().

Some important features of GlideQuery():

• Fail fast: runs into errors quicker than gr, allowing a quick error spotting/correction:

- validates field name

*** Script: Unknown field 'closed_date' in table 'task'. 
<br>Known fields:
[
  "parent",
  "made_sla",
  ...
]

- validates choice values

*** Script: Invalid choice 'donotexist' for field 'approval' (table 'task'). <br>Allowed values:
[
  "not requested",
  "requested",
  "approved",
  "rejected",
  "cancelled",
  "not_required",
  "duplicate"
]: no thrown error

- validates type

** Script: Unable to match value 3 with field 'approval' in table 'task'. Expecting type 'string': no thrown error
Root cause of JavaScriptException: org.mozilla.javascript.NativeError

- validates if any BR is stopping the query

*** Script: {
  "message": "Failure to update table",
  "table": "change_request",
  "lastGlideError": "Operation against file 'change_request' was aborted by Business Rule 'Actual Start/End Date Validation^c83c5e5347c12200e0ef563dbb9a7190'. Business Rule Stack:Actual Start/End Date Validation",
  "changes": {
    "work_end": {},
    "work_start": {}
  }
}: no thrown error

• 100% Javascript: gr is more of Java than Javascript, whereas gq is pure javascript. This has a number of benefits:

- gr almost always returns values differently based on the field returned. For example, first_name in sys_user returns object and not string! Also, glideAggregate returns the count as string😶. GlideQuery on the contrary, returns JSON object as key:value (fieldname:value):

/* returns
{
  "description": null,
  "active": false,
  "parent": {
    "urgency": 3
  },<br>
  "sys_id": "801a087adba52200a6a2b31be0b8f520"
}
*/

• More expressive features: Following are some functions that gives gq an upper hand over gr:

- .map() - can have a function defined inside it to manipulate the data that is received. For example, the following code can return all values in upper case:

new GlideQuery('sys_user')
	.whereNotNull('name')
	.select('name')
	.map(function (user) {return <br>user.name.toUpperCase(); })
	.forEach(gs.log);

- .some(), .every() - these can have conditions in them (as functions) to check if at least one record matches, or if all records match.

- .insert(), .update(), .delete(), .deleteMultiple()

new GlideQuery('sys_user')
	.insert({
    	active: true,
    	name: 'Sam Meylan',
    	city: 'Geneva'
	})
	.get();

- Usage of flags: additional indicators like $DISPLAY, $CURRENCY_CODE, can be added to the field name like below:

new GlideQuery('sys_user')
	.select('company$DISPLAY')
	.forEach(doSomething);

new GlideQuery('core_company')
	.selectOne('market_cap', <br>'market_cap$CURRENCY_CODE')
	.get()

- complex conditions: addCondition, addOrCondition often changes the evaluation logic (mixing ANDs and ORs). This is because in gr, OR takes precedence over AND. gq allows glideQuery inside glideQuery:

// active = true AND (priority = 1 OR severity = 1)
new GlideQuery('incident')
	.where('active', true)
	.where(new GlideQuery()
          .where('priority', 1)
          .orWhere('severity', 1)
          )
	.select('description', 'assigned_to')
	.forEach(doSomething)

PERFORMANCE:

• has an overhead of 4-6% -due to conversions from java to js.

• considering operations performed after the query, gq is way better than gr. Instance#1: gq uses .count() which does not iterate through each record to obtain this value - this is how gr works. Instance#2: selectOne() is the gq equivalent of setLimit(), which we often tend to forget while querying a single record.

WHY I THINK GLIDEQUERY IS WAY BETTER:

• It doesn't have any restrictions to pass to other functions. that's because it returns json, which can be passed to any function.

• Amazing error handling process

• readability

• GQs inside GQs

• clear evaluation of logic with no ambiguity

HOW TO GET GLIDEQUERY IN THE INSTANCE:

1. Install the plugin for Software Asset Management Pro.

2. Run a background script to have it installed(for your personal instance):

var pluginArr = ['com.sn_glidequery'];
var pluginMgr = new GlideMultiPluginManagerWorker(); pluginMgr.setPluginIds(pluginArr); pluginMgr.setProgressName("Plugin Installer"); pluginMgr.setBackground(true); pluginMgr.start();

GlideQuery Cheatsheet:

Snow adventures: https://www.snow-adventures.com/blog/glidequery-cheat-sheet/