Sam

Sam's ServiceNow adventures

A place where to share about ServiceNow platform and technologies

GlideQuery Cheat Sheet

an attempt to document GlideQuery functions and methods

Sam

6 minute read

This is a draft Cheat Sheet for the GlideQuery. Please see this post for a detailled introduction.

This cheat sheet was build with elements gathered from presentations and blog posts and also from reading the script include. It may not be accurate, might evolve and all comments and corrections are welcome !

invoking GlideQuery

In a similar way that GlideRecord, the table name is passed as a parameter:

var myTable = new GlideQuery('table_name');

Cheat sheet

Selections, insert, update

get()

Returns a single record by querying primary key key.

Parameters
Type Name Description
String key (sys_id)
Array selectedFields Additional fields to return in result
Example
var user = new GlideQuery('sys_user')
     .get('5137153cc611227c000bbd1bd8cd2005', ['first_name', 'last_name'])
     .orElse({ first_name: 'Default', last_name: 'User' });

getBy()

Returns a single record, using keyValues as a set of key-values to query by. getBy assumes the ‘=’ operator for each key-value.

Parameters
Type Name Description
Object keyValues Object where the keys are the name of the fields, and the values are the values.
Array selectedFields Additional fields to return in result
Example
var user = new GlideQuery('sys_user')
    .getBy({
        first_name: 'Fred',
        last_name: 'Luddy'
    }, ['first_name', 'last_name', 'city', 'active']) // select first_name, last_name, city, active
    .orElse({
         first_name: 'Nobody',
        last_name: 'Found',
        city: 'Nowhere',
        active: false
    });


insert()

Inserts a single record

Parameters
Type Name Description
Object keyValues Object containing key-values to insert into table
Array selectedFields Fields to return in result Optional
Example
new GlideQuery('sys_user') 
    .insert({
         active: true,
         name: 'Sam Meylan', 
        city: 'Geneva' 
    }) 
    .get();

insertOrUpdate()

Updates an existing record (just like update), however instead of requiring where calls, it uses the primary key(s) in the recordValues object passed in. If the primary key(s) isn’t there, insertOrUpdate will insert a new record instead. Returns an Optional of the newly created/updated record. Often useful when you want to want to ensure a record exists and has the correct values, as you don’t need to check for the record’s existence beforehand.

Parameters
Type Name Description
Object changes Object containing key-values to update/insert into table
Array selectedFields Fields to return in result Optional
Example

// insert a new record
var user = new GlideQuery('sys_user')
    .insertOrUpdate({
        first_name: 'George',
        last_name: 'Griffey'
    })
    .orElse(null);

// update existing record
var user = new GlideQuery('sys_user')
    .insertOrUpdate({
        sys_id: '2d0efd6c73662300bb513198caf6a72e',
        first_name: 'George',
        last_name: 'Griffey' })
    .orElse(null);

update()

Updates an existing record. Requires a where call, specifying all existing primary keys (usually sys_id). Returns an Optional of the newly-updated record.

Parameters
Type Name Description
Object changes Object containing key-values to update/insert into table
Array selectedFields Fields to return in result Optional
Example
new GlideQuery('sys_user')
    .where('sys_id', userId)
    .update({ city: 'Los Angeles' });

updateMultiple()

Updates all records in the table (specified by preceding where clause with the values contained in the changes object. Returns # of records updated.

Parameters
Type Name Description
Object changes Object containing key-values to update/insert into table
Example
new GlideQuery('sys_user')
    .where('active', false)
    .where('last_name', 'Griffey')<r>    .updateMultiple({ active: true });

select()

Specifies which fields to return and returns a Stream containing the results of the query. Note that records aren’t actually read from the database until a terminal Stream method is called (such as reduce() or toArray()). The Stream is intended for reading multiple records in a similar fashion to Java’s Stream class.

Parameters
Type Name Description
…String fields Fields to select
Example
var stream = new GlideQuery('sys_user')
    .select('first_name', 'last_name');

selectOne()

Similar to select(), however only returns an Optional which may contain a single record. This is more efficient than select() if you only need one record, or want to test if a record exists.

Parameters
Type Name Description
…String fields Fields to select
Example
var user = new GlideQuery('sys_user')
    .where('zip', '12345')
    .whereNotNull('last_name')
    .selectOne('first_name', 'last_name')
    .get();

Del()

Deletes all records in the table specified by preceding where clauses

Example
new GlideQuery('sys_user')
    .where('active', true)
    .where('last_name', 'Jeter')
    .del();

Conditions

where()

Returns a new GlideQuery containing where clause

Parameters
Type Name Description
String field Field related to the where clause
Example
new GlideQuery('sys_user')
    .where('active', true)
    .where('last_login', '>', '2016-04-15');

whereNotNull()

Returns a new GlideQuery containing NOT NULL clause

Parameters
Type Name Description
String field Field related to the clause
Example
new GlideQuery('sys_user') 
    .whereNotNull('first_name')

whereNull()

Returns a new GlideQuery containing WHERE NULL clause

Parameters
Type Name Description
String field Field related to the clause
Example
new GlideQuery('sys_user') 
    .whereNull('first_name')

Aggregations

avg()

Returns the aggregate average of a given numeric field

Parameters
Type Name Description
String field Numeric field
Example
var faults = new GlideQuery('cmdb_ci')
    .avg('fault_count')
    .orElse(0);

max()

Returns the aggregate minimum of a given field

Parameters
Type Name Description
String field field
Example
var faults = new GlideQuery('cmdb_ci')
    .max('first_name')
    .orElse('');

min()

Returns the aggregate maximum of a given field

Parameters
Type Name Description
String field field
Example
var faults = new GlideQuery('cmdb_ci')    
	.min('sys_mod_count')
    .orElse(0);

sum()

Returns the aggregate sum of a given numeric field

Parameters
Type Name Description
String field Numeric field
Example
var totalFaults = new GlideQuery('cmdb_ci')
    .sum('fault_count')
    .orElse(0);

count()

Returns the row count of records matching the query

Example
var userCount = new GlideQuery('sys_user')
    .where('active', true)
    .count();

groupBy()

Groups query results. Used with aggregate()

Example
new GlideQuery('task')
    .aggregate('count')
    .groupBy('contact_type')
    .select();

aggregate()

Aggregates a field using an aggregate function. Used to buildqueries which aggregate against multiple fields and/or multipleaggregate functions. If you only need to aggregate against one field with one function, and you don’t need to use groupBy(), then use one of the terminal functions instead: avg() min() max() count()

Parameters
Type Name Description
String aggregateType Aggregate type (‘sum’, ‘avg’, ‘min’, ‘max’, or ‘count’)
String field Field to aggregate
Example
new GlideQuery('task')
    .aggregate('avg', 'reassignment_count')
    .groupBy('contact_type')
    .select();

having()

Filters aggregate groups. Used with aggregate() and groupBy.

Parameters
Type Name Description
String aggregateType Aggregate type (‘sum’, ‘avg’, ‘min’, ‘max’, or ‘count’)
String field Field to aggregate
String operator Only numeric operators allowed: ‘>’, ‘<’, ‘>=’, ‘<=’, ‘=’, and ‘!=’
number value
Example
* new GlideQuery('task')
    .where('description', description)
    .groupBy('priority')
    .aggregate('sum', 'reassignment_count')
    .having('sum', 'reassignment_count', '>', 4)
    .select()

Miscellanious

disableAutoSysFields()

Returns a GlideQuery which does not update sys fields such as sys_created_on, sys_updated_on, and sys_mod_count. This is the equivalent of using autoSysFields(false) with GlideRecord.

Example
new GlideQuery('task')
    .disableAutoSysFields()
    .insert({ description: 'example', priority: 1 });

forceUpdate()

Returns a GlideQuery which forces an update even when no changes are made. Useful when you want to force a business rule to execute.

Example
new GlideQuery('task')
    .forceUpdate()
    .where('sys_id', taskId)
    .update()

orderBy()

Returns a GlideQuery which specifies that the records should be returned in ascending order by a given field.

Example
var query = new GlideQuery('incident')
    .orderBy('number');

orderByDesc()

Returns a GlideQuery which specifies that the records should be returned in descending order by a given field. Can be used with aggregate queries

Example
var query = new GlideQuery('incident')
    .orderByDesc('number');

 new GlideQuery('incident')
    .aggregate('sum', 'child_incidents')
    .groupBy('category')
    .orderByDesc('sum', 'child_incidents')

limit()

Returns a GlideQuery which limits the number of records returned.

Parameters
Type Name Description
[number limit max number of records to return
Example
var incidents = new GlideQuery('incident')
    .limit(20)
    .select('priority', 'description');

withAcls()

By default GlideQuery uses GlideRecord for database interactions. By calling withAcls() GlideQuery will use GlideRecordSecure, which honors ACLs.

Example
var users = new GlideQuery('sys_user')
    .withAcls()
    .limit(20)
    .orderByDesc('first_name')
    .select('first_name')
    .toArray(100);

Subscribe to my newsletter

Say something

Comments powered by Talkyard.

Recent posts

Categories

About

This blog is a personnal blog from Samuel Meylan about ServiceNow technologies and other business related topics.

Sam is a ServiceNow Senior Developper and technical consultant with more than 8 years of experience. He particularly like making integrations and solving complexes requirements.

He also enjoy discovering new and enhanced features shipped with each ServiceNow release.