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'));
var userCount = new GlideQuery('sys_user').count();
b 10b and
b 2b you’ll find that
b 2b 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.
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.
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!