GlideQuery Cheat Sheet

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

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

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

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

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

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

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

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

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

Example

new GlideQuery('sys_user')
    .where('active', true)
    .where('last_login', '>', '2016-04-15');

whereNotNull()

Returns a new GlideQuery containing NOT NULL clause

Parameters

Example

new GlideQuery('sys_user') 
    .whereNotNull('first_name')

whereNull()

Returns a new GlideQuery containing WHERE NULL clause

Parameters

Example

new GlideQuery('sys_user') 
    .whereNull('first_name')

Aggregations

avg()

Returns the aggregate average of a given numeric field

Parameters

Example

var faults = new GlideQuery('cmdb_ci')
    .avg('fault_count')
    .orElse(0);

max()

Returns the aggregate minimum of a given field

Parameters

Example

var faults = new GlideQuery('cmdb_ci')
    .max('first_name')
    .orElse('');

min()

Returns the aggregate maximum of a given field

Parameters

Example

var faults = new GlideQuery('cmdb_ci')    
	.min('sys_mod_count')
    .orElse(0);

sum()

Returns the aggregate sum of a given numeric field

Parameters

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

Example

new GlideQuery('task')
    .aggregate('avg', 'reassignment_count')
    .groupBy('contact_type')
    .select();

having()

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

Parameters

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

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