Click HERE to see how Saviynt Intelligence is transforming the industry. |
08/28/2024 03:19 AM
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}'))"
}
]
Solved! Go to Solution.
08/28/2024 03:42 AM
@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}'))"
}
]
08/28/2024 04:45 AM - edited 08/28/2024 04:54 AM
@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 ?
08/28/2024 05:15 AM
[
{
"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}'))"
}
]
08/28/2024 05:38 AM
[
{
"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}'))"
}
]
08/28/2024 05:31 AM
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
08/28/2024 05:36 AM
Please share logs
08/28/2024 05:42 AM
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}
08/28/2024 05:46 AM
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}'))" } ]
08/28/2024 05:54 AM
@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 !