So let's suppose that we had a GlideRecord for a user — how could we find the roles that user had?
What we'd really like to do is to be able to run some code like this (assuming gr contains the user record we're interested in:
var gru = new GRUtil();
var roles = gru.getM2MList(gr, 'sys_user_has_role', 'user', 'role', 'sys_user_role');
while (roles.next())
gs.log(roles.name);
Here's what that new method getM2MList might look like:
/*
* Returns a GlideRecord instance containing the records related to the given parent (a GlideRecord)
* in the given child table related through the given m2m_table with the given parent reference and
* child reference fields.
*/
getM2MList: function(parent, m2m_table, parent_reference, child_reference, child_table) {
var m2m_gr = new GlideRecord(m2m_table);
m2m_gr.addQuery(parent_reference, parent.sys_id);
m2m_gr.query();
var kids = [];
while (m2m_gr.next())
kids.push('' + m2m_gr.getValue(child_reference));
var kids_gr = new GlideRecord(child_table);
kids_gr.addQuery('sys_id', kids);
kids_gr.query();
return kids_gr;
},
If you haven't seen programs that work with many-to-many tables before, this may look a little odd. The first six lines query the many-to-many table, looking for records whose parent reference points to the given parent. Then we gather up the child references of those records into an array. At the end of those six lines of code, the kids variable contains an array of the sys_ids of all the records in the child table that we want. The last four lines of code simply query the child table for the records with those sys_ids, and return the result.
The same method works equally well for finding the users that have a given role. In fact, that method can be used to query for either side of any many-to-many relationship. If you'd like to see what the many-to-many relationships in your instance are, navigate to System Maintenance → Collections. In this table you'll find a pair of entries for each many-to-many table defined in your instance.
