how to use addJoinQuery?

[patricklatella]

hi all, 

I'm looking run a GlideRecord query where I have a user's sys_id, and I check the [sys_user_grmember] table to see if the user is in a group.  But then I also want to check whether that group is of a certain type, by using an addJoinQuery to the [sys_user_group] table to check the "type" field.  I pulled some info from servicenow Docs, but unclear how to use it in this context. 

Is it possible to use addJoinQuery in this context?  One thing that I wonder about whether it's a problem if my field on the [sys_user_grmember] table is a reference field, and the field that matches on the [sys_user_group] field is a string field...will this prevent addJoinQuery from working?  OR...Anyone see what's missing in my script?  thanks!

//lookup for assignment group
var grp = new GlideRecord('sys_user_grmember');
var grpSQ = grp.addJoinQuery('sys_user_group');//to check the 'type' of the group

//where the gr user from above is the user of the group
grp.addQuery('user',gr.sys_id);//this is the sys_id I've gotten from an earlier GlideRecord

//add the condition that the group is one of 3 types; itsm, hr, or erp_sm
grpSQ.addCondition('typeLIKEitsm');

grp.query();

if (grp.next()) {
current.assignment_group = grp.group;
}

[Inactive_Us2231]

H Patrick, 

This query will probably give you the first record in the sys_user_grmember table. your next query basically checks for all the groups where type is itsm, however this query should happen when the grmember table is actually queried. Hence i dont think join query is actually possible here.

I have tried something which could work for you 

var gr = new GlideRecord("sys_user_grmember");
gr.addQuery('user', userID); //variable for the users sys id which you are getting from an earlier query
gr.query();

while(gr.next()){                 // will give a list of user-group metric
var group = new GlideRecord("sys_user_group");  
group.addQuery('name', gr.group.name);  //  OR you can also use  group.addQuery('sys_id', gr.group); as gr.group will give you the sys id
group.addQuery('u_type', 'type1'); 
group.query();
while(group.next()) {
gs.print('group name is '+group.name);
}
}

i have tried in my personal instance which works

I hope this helps. 

[Slava Savitsky · Accepted solution]

I don't think you need to use the addJoinQuery() method for this. Note that addQuery() supports dot-walking. You should, therefore, be able to achieve what you want with a simple query like this:

var grp = new GlideRecord('sys_user_grmember');
grp.addQuery('user', '<sys_id_of_the_user>');
grp.addQuery('group.type', 'CONTAINS', '<sys_id_of_itsm_type>');
grp.query();

if (grp.next()) {
    current.assignment_group = grp.group;
}

[Allen Andreas]

3 years ago?

[Slava Savitsky]

Might be helpful for somebody else 3 years from now.