Peter Bell
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.
Stream Processing (Part 2) The second half of our Stream introduction. If you haven’t read the first half, I recommend you do so before going forward. In this post, we cover:
reduce flatMap and nested queries Using chunk to improve nested query performance Creating custom Streams reduce Conceptually, a Stream can be viewed as a collection containing the results of a query. However, sometimes we want to boil down (or reduce) this collection of records into a single value.
Stream Processing (Part 1) In this series, I’ve mentioned the Stream class, which is the way GlideQuery users process multiple records and groups returned by the select method. Stream is similar to Java’s Stream class and C#’s IEnumerable interface.
In all the examples so far, I’ve only used Stream’s forEach method, as that’s one of the most common Stream methods available. There are, however, other powerful Stream methods available that can enhance your code.
Dotwalking and Flags Similar to GlideRecord, GlideQuery supports dotwalking, both when using select and where. It dramatically simplifies filtering and reading in fields referenced by the current table instead of executing another GlideQuery. Dotwalking is presumed in the Now platform and should be reasonably familiar to most GlideRecord developers.
Dotwalking Just like GlideRecord, GlideQuery supports filtering using dotwalking:
var tokyoEmployee = new GlideQuery(‘sys_user’) .where(‘company.city’, ‘Tokyo’) .selectOne(‘name’) .get(); When dotwalking, GlideQuery performs the same set of validation checks as regular field names.
Advanced Aggregate Queries As I mentioned in my previous article, GlideQuery supports simple and complex functionality. The simple aggregate functions sum, avg, max, and min are easy to use and have little syntax. However, they have two significant limitations:
They don’t support grouping They support only one aggregation per query Grouping What if I want to count the number of users in each city?
new GlideQuery(‘sys_user’) .aggregate(‘count’) .
Continuing on the journey to explore the GlideQuery Series is Part 3! Time to take out a death star.
Simple Aggregate Queries Sometimes we need aggregate queries (MAX, COUNT, etc.). Fortunately, GlideQuery supports these, while also making them easier to use than ever. For example, compare
var usersGa = new GlideAggregate(‘sys_user’); usersGa.addAggregate(‘COUNT’); usersGa.query(); usersGa.next(); var userCount = parseInt(usersGa.getAggregate(‘COUNT’)); with
var userCount = new GlideQuery(‘sys_user’).count(); Much easier to read!
Inserting, Updating, and Deleting in GlideQuery! Part 2 of the GlideQuery Series. Read Part 1 to catch up.
This is the second in a series of blog posts showing practical examples and patterns using GlideQuery. I believe one of the quickest ways to learn is by diving straight into examples. We’ll start with simple read queries and move on to more advanced topics with each post. Let’s begin!
Inserting new GlideQuery(‘task’) .
My name is Peter Bell. I have been a software developer and tester for the past 16 years for various Seattle area companies. I enjoy cycling, rooting for the Seattle Mariners (not easy), and good beer. My passion for API design and functional programming led me to develop GlideQuery, which we initially used in the ITAM (IT Asset Management) team at ServiceNow. We have expanded its use, both within ServiceNow and to our customers.
