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

[Shishir Srivast]

Can we try like,

var roleB = [];  

var roleAGR = new GlideRecord ('sys_user_has_role');  

roleAGR.addQuery('role', '2010067c4fce0300592d52411310c78d');  

roleAGR.query();  

while (roleAGR.next()){  

var roleBGR = new GlideRecord ('sys_user_has_role');

roleBGR.addQuery('role', 'fadf397c4fce0300592d52411310c7ac');

roleBGR.addQuery('user', roleAGR.user);

roleBGR.query();  

if (roleBGR.next()){  

roleB.push(roleBGR.user.toString());  

}

}  

return roleB;  

[Mussie]

Thanks Shishir, this might work and will let you know. However, do you foresee any performance improvement? I don't see how this will be different from mine.

Any thoughts?

Mussie

[Mussie]

Hi Shishir, I tested it and it works. But the performance hit is much worse than mine. Yours take longer to execute than mine.

[Shishir Srivast]

yes, even i suspected the same since we are trying to do GlideRecord twice and that might cause an performance issue.

I believe as suggested by Gurpreet Singh, GlideAggregate should be better option

[Shishir Srivast]

Can we try with this, let's see if this helps,

var roleB = [];

var query = 'role=2010067c4fce0300592d52411310c78d^ORrole=fadf397c4fce0300592d52411310c7ac';

var roleAGR = new GlideAggregate('sys_user_has_role');

roleAGR.addEncodedQuery(query);

roleAGR.addAggregate('COUNT');

roleAGR.groupBy('user');

roleAGR.query();

while (roleAGR.next()){

if(parseInt(roleAGR.getAggregate('COUNT')) == 2)

roleB.push(roleAGR.user.toString());

}

return roleB;