GlideQuery Cheat Sheet
an attempt to document GlideQuery functions and methods
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);
Comments powered by Talkyard.