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]

Using count didn't make any difference, I am definitely sure that it is the duplicate roles that is causing the extra number of records.

[Shishir Srivast]

Hi Mussie,

Was my last provided code helped to fetch the details? we can consider to check the condition like if(parseInt(roleAGR.getAggregate('COUNT')) >= 2)

[Brian Dailey1]

Hi Mussie,

I think that using the GlideAggregate method mentioned above would lead to invalid results.   Check your User Roles [sys_user_has_role] table and you'll find duplicate entries for many user/role pairs... (e.g.) this means that one user might have two or more entries in the User Role table for just the 'itil' role .... and THAT means your GlideAggregate will return their name even though they might not have the secondary role you are looking for, because they'll have count=2 for only the ITIL role.

I think a better method would to be using a Join query to enforce your criteria.   If you query the User [sys_user] table first and then add a Join query for each of your criteria, it will require that EACH criteria is met in order to display a valid user record.   Here's a quick example that I verified on my PDI, this is just written as a Fix Script to be run quick and simple, but you'll get the idea:

var gr = new GlideRecord('sys_user');
//Add 1st Query Condition using Join
var qc1 = gr.addJoinQuery('sys_user_has_role', 'sys_id', 'user');
qc1.addCondition('role.name', 'knowledge');
//Add 2nd Query Condition using Join
var qc2 = gr.addJoinQuery('sys_user_has_role', 'sys_id', 'user');
qc2.addCondition('role.name', 'itil');
//Now Query
gr.query();
while(gr.next()){
       gs.print(gr.name.toString());
}
gs.print("Users found with both roles: " + gr.getRowCount());

I've queried on 'role.name' in the User Role table just to use the friendly names for clarity, but you could change this to just 'role' and use the sys_id's if you wish (although using hardcoded sys_id's is not ideal).   Please give this a try and let me know if you have any questions.

Thanks,

-Brian