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]

The moment I use, it gives both results, meaning users have roleA or roleB.

[Shishir Srivast]

yes, and i think that's why we need to check if groupby with user has 2 count/user then that should be taken into consideration and it worked for me.

[Shishir Srivast]

Testing Result:

Result:

on Table:

[Gurpreet07]

Ohhh got its... We need to use GlideAggregate then..

getRoleAB: function() {

        var roleA = [];

        var roleB = [];

        var roleAGR = new GlideAggregate('sys_user_has_role');

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

        roleAGR.groupBy('user');

        roleAGR.query();

        while (roleAGR.next()){

                  roleA.push(roleAGR.getElement('user').toString());

        }

        return roleA;

}

[Gurpreet07]

again mistake.... addOrCondition should be addQuery