Mats.search() & supported SQL aggregation functions


#1

Moving on to Event Actions and mats.search():

I can see in "MATS Code Studio Reference – v10.1 " that the COUNT aggregation function is supported for groupings (SQL Group By), and it works as expected.

Are the other standard SQL aggregation functions (MAX, MIN, SUM, AVG) supported as well? If so, what is the correct syntax for using them?

I’ve tried the following with no success. It works, as in no errors are thrown, but the SUM value is always 0. If I replace “SUM” with “COUNT” it does return an accurate count of records.

var timerec_object = mats.ref(‘Time_record’);
var time_records = mats.search
({// get all time records for the timesheet
‘base_object_id’ : timerec_object,
‘selects’ : [ ‘Timerec_timesheet_id’,
‘Timerec_date’,
‘Timerec_sow’,
‘Timerec_activity’,
‘Timerec_subclass’,
‘Timerec_billable_hours’,
‘SUM(Timerec_billable_hours)’,
‘Timerec_nonbillable_hours’
],
‘filters’ : [{ ‘field_path’: ‘Timerec_timesheet_id’,
‘comparator’: ‘equal_to’,
‘value’ : invoice_record.Invoice_timesheet_id
}],
‘groupings’ : [ ‘Timerec_sow’,
‘Timerec_activity’,
‘Timerec_subclass’
],
‘return’ : ‘data’
});// get all time records for the timesheet
mats.log(JSON.stringify(time_records));

Thanks,

Randy


#2

Hi Randy
The issue here is you can’t use references within aggregates in this manner; they can only be used when the reference makes up the full string.

‘SUM(Timerec_billable_hours)’

Easily fixed, though:

‘SUM(' + mats.r('property_id', 'Timerec_billable_hours') + ')’

Mark


#3

Thanks, Mark. I’ll check it out. It will entirely eliminate an ugly bit of code when I get it to work!

Another curiosity question though…

In your example and in the documentation, “mats.r()” is used to get a reference. However, the cut-n-paste example code provided by Code Studio when a reference is defined uses “mats.ref()”. I’ve been using “mats.ref()” since the syntax is cleaner. It seems to have worked in all cases. Is one or the other preferred and/or deprecated? Is there any difference between the two?

Regards,

Randy


#4

Hi Randy

I’m assuming that if you’ve come across mats.ref, you’re on 10.2?

As we moved to globally unique references from 10.1 -> 10.2, mats.ref was added to support it whilst maintaining backwards compatibility.

So whilst mats.r takes two arguments - the type and the reference, mats.ref just takes the reference.

So in your example, it’d be:

mats.ref('Timerec_billable_hours')


Mark


#5

Mark,

Yep, working on 10.2. Being terminally lazy, I much prefer the new version! :slight_smile:

Regards,

Randy