GlideRecord Query Question

[Mussie]

Hi Guys,

I have a user reference field in one of the custom tables I created and this field should only fetch users who have Roles A and B (both).

I am querying sys_user_has_role table but having difficult in fetching the values without writing two queries. I can easily do this if I write two queries using below but this is a bit slow and I was hoping to do this in one query. Any ideas?

Here is what my query looks like at the moment:

getRoleAB: function() {
         var roleA = [];
         var roleB = [];
         var roleAGR = new GlideRecord ('sys_user_has_role');
         roleAGR.addQuery('role', '2010067c4fce0300592d52411310c78d');
         roleAGR.query();
         while (roleAGR.next()){
                   roleA.push(roleAGR.getElement('user').toString());
         }
         var roleBGR = new GlideRecord ('sys_user_has_role');
         roleBGR.addQuery('role', 'fadf397c4fce0300592d52411310c7ac');
         roleBGR.addQuery('user', 'IN', roleA);
         roleBGR.query();
         while (roleBGR.next()){
                   roleB.push(roleBGR.getElement('user').toString());
         }
         return roleBGR;
},

Thank you,

Mussie

[Mussie]

Thanks for the reply, however this gives me less number of users. Why are we breaking when count is < 2? If I don't use that line, I get more people but that is more than the actual figure.

[truptisuryawans]

Hi Mussie

you can use   GlideUser(g User) instead of using GlideRecord which having method hasroles() which is useful for checking multiple roles

[Mussie]

Hi Trupti, thanks for the reply. Can you give me more details please?

[Gurpreet07]

Count < 2 means user having only one role. As we are using orderByAggregate means all user with both the roles (count>1) will come first in the loop and users with only one role will be processed thereafter (count =1)... hence break statement will not process further records once first record with count 1 detected. The script above is for ITIL and admin roles, hope you have changed these as per your requirement.

[Mussie]

What if users have Role A or Role A more than once in their record? It is possible because they are inheriting the roles from their groups and if a user is a member of two groups which contain Role A then there will be duplicate records there. Let me see if Distinct will help in this case.