Consult/Reassign Query - only a user's manager

[DanJ]

Hi

I have a user manager campaign. Let's assume that a specified certifier is unavailable (for whatever reason) to complete their tasks or even to reassign their certification (not uncommon when we have 5000+ certifiers per campaign). I need to allow a campaign admin to reassign a certification ONLY to that certifier's manager.

- If I have no user filter on the reassignment then the campaign admin could reassign to anyone (and they can choose the correct person), but so could the certifier, which is not allowed.

- If I use the loggedInUser variable in the query:

select u from Users u where 1=1 and u.id in (select manager from Users where id=:loggedInUser)

then it works OK for the user, but the campaign admin sees their own manager, not the certifier's manager. Is there an equivalent variable certifier or similar that would let me construct a query only the certifier's manager?

Or can I achieve this in another way? Via some sort of delegation?

[Saathvik]

@DanJ : There is no such variable exposed instead you can use below query which will list manager of the logged in user if user have non-admin SAV Role. otherwise it will list all managers out of which admin can pick respective certifier.

select u from Users u where 1=1 and u.id in (select manager from Users where id=:loggedInUser) UNION select u from Users u where 1=1 and u.id in (select distinct manager from users) and (select count(*) from user_savroles usr where usr.rolekey=<Admin SAV Role Key> and u.userkey=:loggedInUser)=1

[DanJ]

@Saathvik thanks for your response, sorry it took me so long to get back! (holidays!)

That's a clever idea, I see what it is trying to do... I just can't seem to make it work though. Perhaps I am misunderstanding the slightly weird pseudo-sql wrapper. I have successfully constructed an equivalent query in data analyzer but I can't make it work here.  The following will give the manager of the user:

select u from Users u where 1=1 and u.id in (select manager from Users where userkey=:loggedInUser)

The following gives me all user managers

select u from Users u where 1=1 and u.id in (select distinct manager from Users)

but I can't UNION these together. I also can't seem to make it accept any variation of (select count(*) ...) = 1 clause.

Have you verified this works?

[rushikeshvartak]

Do you see any error in logs

[Saathvik · Accepted solution]

@DanJ : Sorry no I haven't personally tried I was thinking of logic and provide a query. But I realized that consult query is HQL which doesn't support UNION so I tried to change the query little bit as below. Can you please validate the same.

select u from Users u where 1=1 and (u.id in (select manager from Users where id=:loggedInUser) or u.id in ((select distinct manager from users) and (select count(*) from user_savroles usr where usr.rolekey=<Admin SAV Role Key> and u.userkey=:loggedInUser)=1))

[DanJ · Accepted solution]

@Saathvik thanks, I didn't know about HQL - very interesting. I have spent a good hour trying so many different subqueries and I think that user_savroles table is not accessible to this query. In this case I have just hardcoded some usernames of the attestation admin team; not ideal, but it works. (I also added a check so that it returns only active users.)

select u from Users u where u.id in (select manager from Users where id=:loggedInUser) or u.id in (select manager from Users where :loggedInUser in (select id from Users where username in ('adminuser1','adminuser2')) and statuskey = 1)

 I might go back to this if I have some time, but it works at least, many thanks 🙂

[rushikeshvartak]

Submit idea ticket to get exposure of additional tables