GlideQuery - Scoped, Global

Implementation

This script include works together with the Stream and Optional APIs in a builder pattern where the method calls chain together, each method building on the returned result of the previous method. Use methods to define the attributes of the query. The methods do not execute until you call a terminal method, a method that returns a query result, allowing you to define the requirements of the query before executing it.

If the query returns a single record, the system wraps the result in an Optional object. If the query returns a stream of records, the system wraps the result in a Stream object. These objects let you manage the result using a set of methods in each API.

For example, this script performs a query on the Task table, groups the records by priority, and returns each priority that has total reassignments greater than four.

var query = new global.GlideQuery('task')
    .where('active', true) //Returns new GlideQuery object with a "where" clause.
    .groupBy('priority') //Returns new GlideQuery object with a "group by" clause.
    .aggregate('sum', 'reassignment_count') //Returns new GlideQuery object with a "sum(reassignment_count)" clause.
    .having('sum', 'reassignment_count', '>', 4) //Returns new GlideQuery object with a "having reassignment_count > 4" clause.
    .select() //Returns a stream of records wrapped in a Stream object.  
    .forEach(function (priority){ //Terminal method in the Stream class that executes the query and returns the result. 
      gs.info("Priority " + priority.group.priority + ": " + priority.sum.reassignment_count + " reassignments");
    });

Priority 1: 11 reassignments
Priority 3: 6 reassignments
Priority 5: 5 reassignments

Error handling

The GlideQuery script include throws an error when your query has a problem and includes a clear explanation to help guide you. This script include checks for:

For example, the following code sample would throw an error because the queried field does not exist in the table.

new global.GlideQuery('task')
    .where('id', '4717dfe5a9fe198100450448b2404c16') // should be 'sys_id'
    .select('description', 'severity')
    .toArray(100);
  // Error: Unable to find field 'id' in table 'task'. Known fields: active, activity_due, ...

This code sample would throw an error because the data type of one of the arguments is incorrect.

new global.GlideQuery('task')
    .where('priority', 'one') // priority is an integer (should be 1)
    .select('description', 'severity')
    .toArray(100);
  // Error: Unable to match value ['one'] with field 'priority' in table 'task'. Expecting type 'integer'

Reuse

Because GlideQuery objects are immutable, you can reuse them later in other parts of your code. For example, this script creates a query and then uses the GlideQuery object later to generate a report.

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

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

Limitations

The GlideQuery script include does not support:

Intermediate and terminal methods in GlideQuery

The GlideQuery script include uses two categories of methods: intermediate and terminal. Intermediate methods are those methods that return a Stream, an API used for interacting with a stream of items like records, allowing a fluent style of syntax where calls are chained together. Terminal methods are those methods that do not return a stream and therefore stop the chain of Stream method calls.

In the GlideQuery script include, methods such as where(), orderBy(), and disableWorkflow() are intermediate methods that return a new GlideQuery object. Similarly, the GlideQuery script include's most popular terminal methods, select() and selectOne(), are terminal methods. These methods are called when GlideQuery is done being configured and is ready to start processing records. The differences between these two method types is important to keep in mind when configuring calls to the GlideQuery script include according to your specific use cases. For more information about intermediate and terminal methods, see the article GlideQuery - Stream Processing Part 1.

The following methods are classified as intermediate:

• aggregate(String aggregateType, String field)
• disableAutoSysFields()
• disableWorkflow()
• forceUpdate()
• groupBy(String fields)
• having(String aggregateType, String field, String operator, Number value)
• limit(Number limit)
• orderBy(String fields)
• orderByDesc(String fieldOrAggregate, String field)
• orWhere(String fieldOrQuery, String operator, Any value)
• orWhereNotNull(String field)
• orWhereNull(String field)
• where(String fieldOrQuery, String operator, Any value)
• whereNotNull(String field)
• whereNull(String field)
• withAcls()

The following methods are classified as terminal:

• avg(String field)
• count()
• deleteMultiple()
• get(String key, Array selectedFields)
• getBy(Object keyValues, Array selectedFields)
• insert(Object keyValues, Object selectedFields)
• insertOrUpdate(Object changes, Object selectedFields)
• max(String field)
• min(String field)
• select(String fields)
• selectOne(String fields)
• sum(String field)
• toGlideRecord()
• update(Object changes, Object selectedFields)
• updateMultiple(Object changes)