Counting records with GlideAggregate

Earlier in this chapter, the getRowCount function of GlideRecord was introduced. It returns the number of results found. However, this is only determined by getting all the information from the database and then counting it. Wouldn't it be more efficient if we could get just the total number? We can, with GlideAggregate!

Tip

The developer site has more information available: https://developer.servicenow.com/app.do#!/api_doc?v=helsinki&type=server&scoped=true&to=class__scoped_glideaggregate__helsink.

Run the following lines of code to get the total number of records that were created yesterday:

var today = new GlideDate();
 
 var yesterdayTime = new GlideDateTime();
 yesterdayTime.addDaysUTC(-1);
 
 var yesterday = new GlideDate();
 yesterday.setValue(yesterdayTime);
 
 
 var count = new GlideAggregate('x_hotel_check_in');
 count.addQuery('sys_created_on', '<', today);
 count.addQuery('sys_created_on', '>=', yesterday);
 
 count.addAggregate('count');
 count.query();
 count.next();
 var result = count.getAggregate('COUNT');
 gs.info('Result: ' + result);

Note

With GlideRecord, you should check that the next function is successful. But in this scenario GlideAggregate will always return something.

The style of working with GlideAggregate is very similar to that for GlideRecord. You can add filters, for example, in just the same way. Here, one of the functions of GlideSystem is used to return all the records created after the beginning of yesterday (that is, yesterday midnight). The main difference is that the addAggregate function is used to ask for the desired information, and the query call then returns that instead of a list of fields.

The addAggregate function accepts two parameters: the calculation (such as min, max, count, sum, or avg for average) and the field to perform it on. The groupBy function is used to divide the result set up and return multiple entries, which are then looped over.

To obtain the last time every guest checked in, the following code could be used:

var gr = new GlideAggregate('x_hotel_check_in'); 
gr.addAggregate('max', 'sys_created_on'); 
gr.groupBy('guest'); 
gr.query(); 
while(gr.next()) { 
  gs.log(gr.guest.name + ' ' + gr.getAggregate('max', 'sys_created_on')); 
}

Be aware that GlideAggregate only populates attributes in the object that are relevant. So you can only access the fields that you group by or have an aggregate of. In the preceding example, guest is available since it is grouped by field. Other fields are not available.