Glidequery - Complex Queries

#glidequery , #scripting

Peter Bell

4 minute read

Using “or” and Complex Queries

Sometimes when filtering data we need to express when expressions A or B are true. When we add multiple where clauses to our query, it’s implied that they stand in an AND relation to each other. But what if we want one or the other? For this, we can simply call orWhere:

var numBigCompanies = new GlideQuery('core_company')
    .whereNotNull('parent')
    .orWhere('num_employees', '>', 5000)
    .count();

Here we count the number of companies that have a parent company or have more than 5000 employees. By simply changing where to orWhere we make it clear in plain English the two clauses should be or’d together.

There are a few rules when using orWhere (or its sibling functions orWhereNull and orWhereNotNull):

1. The first orWhere in a query must be preceded by a where, whereNull, or whereNotNull.
2. No where clauses can be used after an orWhere clause.

The first rule is to make the GlideQuery script more readable. It doesn’t make sense to say “orWhere” if we didn’t already state a prior where clause. It is often signals a mistake if somewhat begins their query with orWhere. The second rule means that all where clauses (and other related clauses) must be AND’d or OR’d, but you cannot have a combination of the two. So for example:

// WHERE priority = 1 AND urgency = 1 AND escalation = 3
new GlideQuery('task')
    .where('priority', 1)
    .where('urgency', 1)
    .where('escalation', 3)

// WHERE priority = 1 OR urgency = 1 OR escalation = 3
new GlideQuery('task')
    .where('priority', 1)
    .orWere('urgency', 1)
    .orWhere('escalation', 3)

// Ambiguous query: WHERE priority = 1 OR urgency = 2 AND escalation = 3
new GlideQuery('task')
    .where('priority', 1)
    .orWhere('urgency', 1)
    .where('escalation', 3)

The first two are queries are fine, but the third would throw an error. Why does GlideQuery have to be so rigid? Because GlideQuery isn’t entirely sure how you’re grouping the three where clauses and wants to avoid any mistakes. Should it be understood as

WHERE priority = 1 OR (urgency = 1 AND escalation = 3)

or

WHERE (priority = 1 OR urgency = 1) AND escalation = 3

???

How you group the filters changes the behavior of your application. There is some ambiguity in the query, and ambiguity is an opportunity for bugs. SQL and GlideRecord address this ambiguity by using operator precedence. In SQL, AND has precedence over OR, and so the parenthesis would wrap around the AND expression (the first example). However, GlideRecord gives OR precedence, doing the opposite (as in the second example). Having used SQL for years I assumed GlideRecord would behave the same way as SQL, but they’re different.

So does GlideQuery does give AND or OR precedence? Neither! In the spirit of Fail Fast, we ask the developer to explicitly group their filter expressions, and throw an Error if they don’t. But how do you group where clauses in GlideQuery, since you only allow AND or OR, but not both in a single GlideQuery? By nesting GlideQuery:

// WHERE (priority = 1 OR urgency = 1) AND escalation = 3
new GlideQuery('task')
    .where(new GlideQuery()
        .where('priority', 1)
        .orWhere('urgency', 1))
    .where('escalation', 3)

// WHERE priority = 1 OR (urgency = 1 AND escalation = 3)
new GlideQuery('task')
    .where('priority', 1)
    .orWhere(new GlideQuery()
        .where('urgency', 1)
        .where('escalation', 3))

The nested GlideQuery can be seen as a grouped set for where clauses. Another way to look at it is that nested GlideQueries passed to a where clause are conceptually placed within parenthesis. This makes it explicit how you want the clauses grouped together.

Note in the above examples that the nested GlideQueries have no table name passed to their constructors. Because its inside of another GlideQuery’s where clause, it assumes the parent’s table. However, you could pass “task” in the nested GlideQuery’s constructor, or a base table name if one exists (task has no base class). This allows us to reuse a GlideQuery within another GlideQuery, for example:

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

pri1Tasks
    .select('number')
    .forEach(function (task) { gs.info(task.number); });

// Note that incident is a subclass of task
new GlideQuery('incident')
    .where(pri1Tasks)
    .orWhere('incident_state', 1)
    .orderBy('severity')
    .select('number')
    .forEach(doSomething);

Because of some limitations of the platform the nested GlideQueries themselves cannot have nested GlideQueries.

new GlideQuery('task')
    .where('priority', 1)
    .orWhere(new GlideQuery()
        .where('impact', 1)
        .where(new GlideQuery() // Error: we can't nest this far in
            .where('active', true)
            .orWhere('urgency', 2)))

Conclusion

GlideQuery supports both filtering using AND and OR expressions. I personally find the syntax easier to reason about, and I hope you do too. GlideQuery can leverage other GlideQuery objects within where clauses, allowing for grouped sets of expressions.

Comments