Glidequery Part 3

#glidequery , #scripting

Peter Bell

3 minute read

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! And GlideQuery’s count returns a JavaScript number, whereas getAggregate always returns a string. Failing to parse getAggregate’s string on numeric values can lead to some nasty, enigmatic bugs. For example, when comparing the strings b10b and b2b you’ll find that b2b is greater, because comparisons are done alphabetically for strings.

What about the other aggregate functions supported by the platform (MAX, MIN, AVG, and SUM)? These are similar to count, except that they return an Optional (which we’ll cover in more detail in the future). For example:

var totalProfits = new GlideQuery('core_company')
    .sum('profits')
    .orElse(0);

Why have sum return an Optional instead of a number? The reason is that sometimes no rows match a given query. In the above example, if there are no companies found, should the total be zero? In this case yes, however that may not always be true, and having the developer handle that scenario makes it more explicit. If we always returned zero when no rows existed, there would be ambiguity: Is it zero because there are no rows, or because all the rows had $0.00 profits? Perhaps the best course of action in another application would be to call Optional’s get method, which will throw when no companies are found. In sum, Optional is empty when no rows match the aggregate query.

Type Checking

GlideQuery does type checking with the numeric aggregate queries sum and avg:

var averageName = new GlideQuery('core_company')
    .avg('name')
    .orElse(0);

// Error: Aggregate function 'avg' can only be used on fields with
// a type from one of the following: integer, longint, float, double,
// currency. Field name has type string.

It makes no sense to have an average string, so we throw an Error because this was probably a mistake. It does, however, make sense to have a max or min string, so those aggregate functions would work here.

Conclusion

GlideQuery’s simple aggregate support has a concise syntax which can handle many application needs. However, sometimes we need more advanced tools such as groupBy and having. Also, what if we want to do multiple aggregate queries at once, which isn’t supported in the above functions? Stay tuned for advanced aggregate queries!

Comments