Mats.search on relation

I have a ‘expert_type’ object with a one-to-many relation to a subcategory.

I am receiving the values within a data processor, where I need to do some validation and manipulation.

I can verify that the category object exists using mats.search:

var a = mats.search({
   'base_object_id':'expert_type_object',
   'selects': ['COUNT(*)'],
   'return': 'value',
   'filters': [{'field_path': 'expert_type', 'value': transformed_fields.expert_type, 'comparator': 'equals'}]
});

if (a === '0') {
    errors += "Expert type " + transformed_fields.expert_type + " not present\r\n";
}

I can also retrieve the ID of the expert type.

Finally I can also verify the sub_category exists as above, but I am struggling with the search syntax to ensure the subcategory exists under the expert-type’

var a = mats.search({
   'base_object_id':'expert_sub_category_object',
   'selects': ['COUNT(*)'],
   'return': 'value',
   'filters': [{'field_path': 'expert_sub_category', 'value': transformed_fields.expert_sub_category, 'comparator': 'equals'},
               {'field_path': 'expert_subcategory_to_category', 'value':??ID}]   <=== What goes here?
});

I have a reference for the relation path between the objects (expert_subcategory_to_category) but need some help with the syntax of the search (Please…)

Hi Richard,

I have seen your query and will reply as soon as I have an answer for you.

Regards - MP

After many hours of trial and error I came up with a solution, so to save anyone else the time, here it is:

var a = mats.search({
   'base_object_id':'expert_sub_category_object',
   'selects': [':id', mats.r('relation_path', 'expert_subcategory_to_category')+':id'],
   'return': 'row',
   'filters': [{'field_path': 'expert_sub_category', 'value': transformed_fields.expert_sub_category, 'comparator': 'equals'}]
});
if (a === null) {
    errors += "Expert sub category " + transformed_fields.expert_sub_category + " does not exist\r\n";
}
else {
    if (a[mats.r('relation_path', 'expert_subcategory_to_category')+':id'] != etID) {
        errors += "Expert sub category " + transformed_fields.expert_sub_category + " not present under expert_type " + transformed_fields.expert_type + "\r\n";
    } 
}

So what does it do…

We do a search for sub-category filtered on the name and we select to return the id of the subcategory as well as the id of the parent record. As we are going up the tree there can only be zero or one entries returned (Sub categories are supposed to be unique), so we return a ‘row’.

Then we check the results - a null return means no sub category exists, non-null that it does, so we just compare the ID of the parent category with the one we already have.

All the bits were in the documentation or the forum, I was just having problems putting it together! :joy:

1 Like