Overview of GlideQuery

This overview is mostly a coming from the breakout presentation done by Peter Bell during knowledge 2020.

Currently this feature is not available in the platform but is part of the ITAM plugin. However it will likely be available in future releases like Paris or Quebec.

In the meantime, it is possible to activate GlideQueryin your personal instance. See below the instructions to do so.

There is also a Cheat Sheet available for help you playing around with this functionnality.

What is GlideQuery

• GlideQuery is Server script API
  • it consists of 2 global script includes:
    • GlideQuery, to which we can have a look (non protected)
    • GlideQueryEvaluator, which is protected, therefore it is not possible to have a look at it.
• It is said that it use GlideRecord under the hood, probably inside the GlideQueryEvaluator script
• It also replace GlideAggregate in some cases (in particular for counting records)
• it is 100% JavaScript.

So it will not replace GlideRecord, but it proposes an additional layer on the top of it, that offers several benefits to the developers. Mainly it should reduce the likelihood of errors and mistakes when manipulating GlideRecord and make it’s usage clearer and easier.

Three principles of GlideQuery

Peter Bell states that GlideQuery has 3 principles, that are:

• Fail fast: runs into errors as soon as possible, offering a quick feedback loop
• Be JavaScript: behave like a normal JavaScript library. The GlideRecord is sometime confusing and behave more like Java.
• Be expressive: allows to do more with less code

We will se below more in details what that means.

Fail fast

By “Fail fast” it means that the query should run into error if anything is potentially wrong and therefore this can be spotted and corrected early. For example:

• Field name checking
• Choice value checking
• Type checking
• Check if any Business Rule rejects the update/insert

Here below are some examples that shows the differences between GlideRecord and GlideQuery

Field Name Checking

GlideQuerychecks that the field name used in the query are valid and if not it will raise an error.

For example, running the following code with GlideRecord, it will delete all the records from the Users table, even if the field activated is wrong:

var gr = new GlideRecord('sys_user');
gr.addQuery('actived', '!=', true);
gr.query();
gs.info(gr.getRowCount());
gr.deleteMultiple();

If we run the equivalent with GlideQuery, it will fail and give an error instead:

new GlideQuery('sys_user')
	.where('activated', '!=', true)
	.del();

This will return an error:

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

Choice value checking

GlideQueryis also checking for the choice value when choice fields are involved in the query.

For example, running the following code with GlideRecord, it will not return anything.

var gr = new GlideRecord('task');
gr.addQuery('approval', 'donotexist'); // we use an invalid value for the choice field 'approval'
gr.query();
while (gr.next()) {
	gs.info(gr.getDisplayValue());
}

If we run the equivalent with GlideQuery, it will fail and give an error instead:

var tasks = new GlideQuery('task')
	.where('approval', 'donotexist') // we use an invalid value for the choice field 'approval'
	.select('number')
	.forEach(gs.log);

This will return an error:

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

Strangely, it seems to work only with choice fields that have String value. For example this code do not raise any error, despite the state 8 doesn’t exist

var tasks = new GlideQuery('task')
	.where('state', 8 /*do not exist!*/)
	.select('number')
	.forEach(gs.log);

Type checking

GlideQueryis also checking if there the value is of the correct type. For example:

var tasks = new GlideQuery('task')
	.where('approval', 3) // we pass an Integer instead of a String
	.select('number')
	.forEach(gs.log);

Will return an error:

** 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

Check if any Business Rule rejects the update/insert

This is another difference with GlideRecord: when a Business Rule abort the insert or update action, then the script would continue, ignoring this error. With GlideQuery, it would rather stop.

For example, running the following code with GlideRecord, it will continue executing, despite that a business rule aborted the action due to start date being after the end date.

var gr = new GlideRecord('change_request');
gr.get('c83c5e5347c12200e0ef563dbb9a7190');
gr.setValue('work_end', '2016-01-01');
gr.setValue('work_start', '2020-01-01');
gr.update();

gs.info("executing this line?"); //yes, it will execute this line despite the error in the Business rule preventing the start data after the end date

If we run the equivalent with GlideQuery, it will fail and give an error instead:

new GlideQuery('change_request')
	.where('sys_id', 'c83c5e5347c12200e0ef563dbb9a7190')
    .update({
    	work_end: new GlideDateTime('2016-01-01'),
    	work_start: new GlideDateTime('2020-01-01')
    });

gs.info("executing this line?");

This will return an error:

*** 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

Be JavaScript

Stringly typed values

WIth GlideRecord there is frequently issues with the type of value returned. For example:

var gr = new GlideRecord('sys_user');
gr.addQuery('first_name', 'Abel');
gr.query();

if (gr.next()) {
    gs.info(gr.first_name); // -> Abel
    gs.info(gr.firt_name === 'Abel'); // -> false 🤔
    gs.info(typeof (gr.first_name)); // -> object
}

The reason is that the first_nameis a java object. Another example with GlideRecord:

var gr = new GlideRecord('sys_user');
gr.query();
if (gr.next()) {
	if (!gr.getValue('active') { // will return "0" or "-1" as string, and it evaluated always as true...
		//doSomething(gr); 
	}
}

Last example with GlideAgregate

var taskGa = new GlideAgregate('task');
taskGa.addAggregate('COUNT');
taskGa.query();
taskGa.next();
var rowCount = taskGa.getAggregate('COUNT'); // -> return a String ("123")

// With GlideQuery
var rowCount = GlideQuery('task').count(); // -> return a number (123)

With GlideQuery all this example are solved: it return a javascript object, where the key is the field name and the value the actual value of the field:

var gr = new GlideQuery('task')
	.whereNotNull('parent');
	.selectOne('description', 'active', 'parent.urgency')
	.get()
gs.info(JSON.stringify(gr));

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

Be expressive

For reading the data, there is Stream, that apply when reading multiple records and Optional, used for a single record.

Here below are code examples that shows some of the methods in action.

Example with map and forEach

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

// will return a list of name in upper case

Example with some and every

var hasOnlyShortDescription = new GlideQuery('task')
	.whereNotNull('description')
	.select('description')
	.every(function (task) { return task.description.length < 10; })
// checks that all records match the condition

var hasLongDescriptions = new GlideQuery('task')
	.whereNotNull('description')
	.select('description')
	.some(function (task) { return task.description.length < 1000; })
// checks that at least one record matched the condition

Examples with insert, update, delete

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

// delete
new GlideQuery('task')
	.where('priority', 5)
	.disableWorkflow() // disable the business rules
	.deleteMultiple();

// update
new GlideQuery('incident')
	.where('sys_id', id)
	.update({priority: 1});

new GlideQuery('incident')
	.where('priority', '>' 2)
	.updateMultiple({order: 1});

Examples with aggregation

var companyCount = new GlideQuery('core_company')
	.where('city', 'Berlin')
	.count();

var maxReopenCount = new GlideQuery('incident')
	.where('priority', 3)
	.max('reopen_count')
	.orElse(0);
// will return the max count or 0 if there is no

new GlideQuery('task')
	.where('active', true)
	.groupBy('priority')
	.aggregate('sum', 'reassignment_count')
	.having('sum', 'reassignement_count', '>', 4)
	.select()
	.toArray(10)
/* return
[
  {
    "group": {
      "priority": 1
    },
    "sum": {
      "reassignment_count": 11
    }
  },
  {
    "group": {
      "priority": 2
    },
    "sum": {
      "reassignment_count": 3
    }
  },
  ...
  */

Flags

Flags are additional indicators that can be added to the field name to request thinks like the display ($DISPLAY) value or the currency code ($CURRENCY_CODE).

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

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

Complex queries

Complex queries are queries involving OR conditions or nested conditions.

For example with GlideRecord:

var taskGr = new GlideRecord('incident');
taskGr.addQuery('active', true)
	.addCondition('priority', 1)
	.addOrCondition('severity', 1);

How will this be evaluated? it is ambiguous and it could be :

1. (active = true AND priority = 1) OR severity = 1
2. active = true AND (priority = 1 OR severity = 1)

The correct answer is the second one. With GlideRecord, OR has priority over AND.

With GlideQuery, the ambiguity is reduced and things are clearer:

// 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', 'assgined_to')
	.forEach(doSomething)

// manager is NULL OR ( title = 'Vice President' AND state = 'CA')
new GlideQuery('sys_user')
	.whereNull('manager')
	.where(new GlideQuery()
          .where('title', 'Vice President')
          .orWhere('state', 'CA')
          )
	.select('name')
	.forEach(doSomething)

The main difference is the use of a GlideQuery inside the GlideQuery. This force the parenthesis explicitly in the query.

performance

The use of the `GlideQuery’ add an overhead of about 4% to 6%, mainly due to the conversions done from java to javascript.

But we need to keep in mind that in many cases we would do this conversions anyway after the query, when using the data retrieved from the database.

Also it force good practices from a performance point of view. For example, when querying for a single record with GlideRecord, we should add .setLimit()but it is often forgotten. GlideQueryuses selectOne() for this purpose.

Also the getRowCountshould not be used to count the record, as the underlying query to the database will query all the records. The GlideAggregatewith a countshoud be used instead. GlideQueryoffers here an easiest way to achieve this with .count().

Immutability and reuse

The GlideQueryis an immutable object. This means that it can be passed to a function and we don’t care what the function is doing with it, unlike with GlideRecord.

It allows also to reuse the query and extend it. Here below some examples:

var highPriorityTasks = new GlideQuery('task')
	.where('active', true)
	.where('priority', 1);
generateReport(highPriorityTasks);
notifyOwner(highPriorityTasks);

var avgReassignmentCount = highPriorityTasks
	.avg('reassignment_count')
	.orElse(0)

How to enable it on your instance (for testing)

So now that we went through a lot of explanations, you will probably want to try by yourself. As stated at the beginning, it is (still) not included in a vanilla instance, but it is part of the ITAM plugin.

Luckily, despite being part of the ITAM plugin, it is possible to enable GlideQuery on your instance and play with it.

Do to so, run the following script:

⚠Try this in your personal instance only. I would not recommend to use this in a productive environment at the moment. It will likely be evolve and will eventually be integrated in Paris or Quebec release.

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

Checking the code

Now that is it installed, you can go in the script includes and look for the GlideQuery script include. It is open and you can read all the code. It is quite interesting well documented. It can also gives you some insights about what can be done and how to use it.

Cheat Sheet

There is a Cheat Sheet available for help you playing around with this functionnality.

References

1. Peter Bell, breakout GlideQuery: A modern upgrade to GlideRecord CCB3052, accessed 2020 06 12
2. Jace Benson, What is GlideQuery, 2020 04 28