Filter Users on Update user form based on organization owners

[ArW]

Hello,

Following the answer posted in this topic
Solved: Filter Users on Update user form - Saviynt Forums - 97270

I am trying to use SQL queries to filter users for the update user form based on organization owners. 
My usecase is the following:

All organization owners have a specific SAV Role
This SAV Role allows them to create and update workers based on their organization.
-> If I am an owner in organization orgA, I should be able to update all users attached to organization orgA when I have this SAV Role

I have already tried this query that I saw in the topic I mentionned. It is working as expected.

[
    {
        "for":"UpdateUserRequest",
        "query":"select a from Users a where a.id in (select cu.userkey from Customer_Users cu where cu.customerkey in (select c.id from Customer c where c.customername='${users.customer}'))"
    }
]

I have tried this request, trying to follow the syntax of the request that worked but I can't seem to make it work. Do you have any idea where the issue can come from ? When modifying it slightly (because of the select a from Users where a.id) it does work in the data analyzer

[
    {
        "for":"UpdateUserRequest",
        "query":"select a from Users a where a.id in (select cu.userkey from Customer_Users cu where cu.customerkey in (select c.customerkey FROM organization_owners oo, customer c, users u WHERE oo.customerkey = c.customerkey AND oo.userkey = u.userkey AND u.userkey = '${users.userkey}'))"
    }
]

[Raghu]

@ArW  try below

[
{
"for":"UpdateUserRequest",
"query":"select a from Users a where a.id in (select cu.userkey from Customer_Users cu where cu.customerkey in (select c.id FROM organization_owners oo, customer c, users u WHERE oo.customerkey = c.customerkey AND oo.userkey = u.userkey AND u.userkey = '${users.userkey}'))"
}
]

[ArW]

@Raghu thanks for your reply, unfortunately it does not work

I have realized than my request was a bit overcomplicated.
This request works in the data analyzer when replacing <USERKEY> by my user userkey

select u.userkey, u.username from users u where u.userkey in (select userkey from customer_users where customerkey in (select customerkey from organization_owners where userkey= '<USERKEY>'))

Based on the exemple from the other topic, I guess that the syntax select a from Users a where a.id in is specific to this type of requests so I tried to stick to it to implement my Data analyzer request. I obtain this result:

[    
    {    
    "for":"UpdateUserRequest",
    "query":"select a from Users a where a.id in (select userkey from customer_users where customerkey in (select customerkey from organization_owners where userkey= '${users.userkey}'))"
    }
]

However, this does not as well
Maybe the issue is that to access my current user userkey, ${users.userkey} does not work ?

[rushikeshvartak]

[    
    {    
    "for":"UpdateUserRequest",
    "query":"select a from Users a where a.id in (select userkey from customer_users where customerkey in (select customerkey from organization_owners where userkey= '${users.id}'))"
    }
]

[rushikeshvartak]

@ArW 

[    
    {    
    "for":"UpdateUserRequest",
    "query":"select a from Users a where a.id in (select userkey from Customer_users where customerkey in (select customerkey from Organization_owners where userkey= '${users.id}'))"
    }
]

[ArW]

Hello @rushikeshvartak 

Unfortunately this does not work as well
I tried to use the HQL tab instead of the JSON one too but my query is not accepted

[rushikeshvartak]

Please share logs

[ArW]

2024-08-28T14:39:32+02:00-ecm-services.ArsRequestAllowedService-http-nio-8080-exec-24-474lv-DEBUG-map:[MODIFYREQUEST:false, STAT:0, WCAT:0, REQ_QRY_LIST:[[ { "for":"UpdateUserRequest", "query":"select a from Users a where a.id in (select userkey from customer_users where customerkey in (select customerkey from organization_owners where userkey= '4276'))" }]]]
2024-08-28T14:39:32+02:00-ecm-services.ArsRequestAllowedService-http-nio-8080-exec-24-474lv-DEBUG-Query JSON:[[query:select a from Users a where a.id in (select userkey from customer_users where customerkey in (select customerkey from organization_owners where userkey= '4276')), for:UpdateUserRequest]] | params.tileType:UpdateUserRequest
2024-08-28T14:39:32+02:00-ecm-services.ArsRequestAllowedService-http-nio-8080-exec-24-474lv-DEBUG-customQry ( a.id in (select userkey from customer_users where customerkey in (select customerkey from organization_owners where userkey= '4276')) )

@rushikeshvartak 
This is with your solution by using ${users.id}

[rushikeshvartak · Accepted solution]

Use below

[    
    {    
    "for":"UpdateUserRequest",
    "query":"select a from Users a where a.id in (select userkey from Customer_users where customerkey in (select customerkey from Organization_owners where userkey= '${users.id}'))"
    }
]

[ArW · Accepted solution]

@rushikeshvartak 
You were right, the issue was indeed coming from the case of the table names
The case must be like so: Customer_Users or Organisation_Owners
Customer_users or Organisation_owners does not work

The final working json object is this:

[    
    {    
    "for":"UpdateUserRequest",
    "query":"select a from Users a where a.id in (select userkey from Customer_Users where customerkey in (select customerkey from Organization_Owners where userkey= '${users.id}'))"
    }
]

Thanks a lot for your help !