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

[Gurpreet07]

Hi Mussie,

I have tested the below code and its working fine. As we are using Aggregate, It will not impact performance. Here as we are using groupBy along with GlideAggregate, hence the addQuery will be applied to group of records and not on single record.

getRoleAB: function() {

        var roleA = [];

        var roleAGR = new GlideAggregate('sys_user_has_role');

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

        roleAGR.addQuery('role', 'fadf397c4fce0300592d52411310c7ac')

        roleAGR.groupBy('user');

        roleAGR.query();

        while (roleAGR.next()){

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

        }

        return roleA;

}

[Mussie]

I test this and it doesn't work, because you are looking for a record which fulfils both conditions (role A and B), which is impossible.

[Gurpreet07]

Yes you are right. Finally below query in background script returns all users with role ITIL and ADMIN

function getRoleAB() {

        var roleA = [];

        var roleAGR = new GlideAggregate('sys_user_has_role');

        roleAGR.addQuery('role', '282bf1fac6112285017366cb5f867469').addOrCondition('role', '2831a114c611228501d4ea6c309d626d'); //ITIL,Admin

        roleAGR.addAggregate('COUNT','role');

        roleAGR.addHaving('COUNT', '>', 1);

roleAGR.groupBy('user');

        roleAGR.query();

        while (roleAGR.next()){

gs.print('1');

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

        }

        return roleA;

}

gs.print(getRoleAB());

[Mussie]

Hi Gurpreet, unfortunately addHaving doesn't work in Scoped apps. I am getting an error message 'Function addHaving is not allowed in scope x_hr_00003'.

[Gurpreet07]

Ohhh..... Still there's some hope with GlideAggregate - Scoped function orderByAggreegate

GlideAggregate - Scoped

You probably need to compare the response time of the logic below with the one you already have. Below code in Background script provides all users with ITIL and Admin role.

function getRoleAB() {

        var roleA = [];

        var roleAGR = new GlideAggregate('sys_user_has_role');

        roleAGR.addQuery('role', '282bf1fac6112285017366cb5f867469').addOrCondition('role', '2831a114c611228501d4ea6c309d626d'); //ITIL,Admin

        roleAGR.addAggregate('COUNT','role');

        roleAGR.orderByAggregate('count', 'role');

        roleAGR.groupBy('user');

        roleAGR.query();

        while (roleAGR.next()){

var count = roleAGR.getAggregate('COUNT', 'role');

if(count<2){

break;

}

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

        }

        return roleA;

}

gs.print(getRoleAB());