Config for Requestable Entitlement in ARS query for comma separated values

[nmuzinic]

We have a scenario where in user customproperties (46 and 47) we store comma-separated country codes (eg: DZ,AO,AI) and region (EMEA,AP). We mapped the entitlements in the same way, added country codes and region values in the entitlement customproperties (10 and 11), what we want is when users are requesting in ARS to only see the entitlements that match any of these conditions. Is there a way  to configure Config for Requestable Entitlement in ARS query in such a way? We have no issues with filtering entitlements this way when there is only one value but we just can't get the query right with multiple/comma separated values.

[rushikeshvartak]

use FIND_IN_SET

[nmuzinic]

Hi @rushikeshvartak,

Thanks, it works but not without issues 🙂 So my query looks like this:

ev.customproperty23 in (FIND_IN_SET((select customproperty46 from Users where userkey in (${requestee})),ev.customproperty23)) or ev.customproperty19 in (FIND_IN_SET((select customproperty8 from Users where userkey in (${requestee})),ev.customproperty19))

And this works fine when there are no special characters, but i have a value with / char e.g. Test 1/2, as far as I can see FIND_IN_SET supports special chars:

I did find this error in log viewer:

2024-02-01T15:39:27+01:00-arsms-c.s.s.a.s.impl.EndpointsServiceImpl-http-nio-8787-exec-3-q59gv-ERROR-Error in getEntitlementValuesCount :: {}|java.lang.IllegalArgumentException: org.hibernate.QueryException: unexpected char: '{' [Select ev.entitlementtypekey.id, count(distinct ev.id) from com.saviynt.ssm.entity.EntitlementValues ev where ev.entitlementtypekey.endpointkey.id = :endpointKey and ((ev.entitlementtypekey.id =29 and (ev.customproperty23 in (FIND_IN_SET((select customproperty46 from com.saviynt.ssm.entity.Users where userkey in (${requestee})),ev.customproperty23)) or ev.customproperty19 in (FIND_IN_SET((select customproperty8 from com.saviynt.ssm.entity.Users where userkey in (${requestee})),ev.customproperty19))) )) and ev.status=1 group by ev.entitlementtypekey.id]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:729) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:104) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at jdk.internal.reflect.GeneratedMethodAccessor109.invoke(Unknown Source) ~[na:na]|Caused by: org.hibernate.QueryException: unexpected char: '{' [Select ev.entitlementtypekey.id, count(distinct ev.id) from com.saviynt.ssm.entity.EntitlementValues ev where ev.entitlementtypekey.endpointkey.id = :endpointKey and ((ev.entitlementtypekey.id =29 and (ev.customproperty23 in (FIND_IN_SET((select customproperty46 from com.saviynt.ssm.entity.Users where userkey in (${requestee})),ev.customproperty23)) or ev.customproperty19 in (FIND_IN_SET((select customproperty8 from com.saviynt.ssm.entity.Users where userkey in (${requestee})),ev.customproperty19))) )) and ev.status=1 group by ev.entitlementtypekey.id]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:235) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:143) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]| at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:611) ~[hibernate-core-5.3.18.Final.jar!/:5.3.18.Final]|

Any idea how to get around this issue?

[rushikeshvartak]

REPLACE(ev.customproperty23, '/', '') IN (
    SELECT REPLACE(customproperty46, '/', '')
    FROM Users
    WHERE userkey IN (${requestee})
  )
  OR
  REPLACE(ev.customproperty19, '/', '') IN (
    SELECT REPLACE(customproperty8, '/', '')
    FROM Users
    WHERE userkey IN (${requestee})
  )

[nmuzinic]

Hi  @rushikeshvartak,

I've actually tried that but I thought that I got the syntax wrong, but it seems this is not acceptable syntax for Config for Requestable Entitlement in ARS query. And again I need to combine FIND_IN_SET in there to get desired result

[rushikeshvartak]

You can't use string functions to inner query 

[nmuzinic]

Well not in Saviynt, anywhere else yes:

Another very simple issue that can't be resolved in Saviynt other than changing the data which of course is not an option.

[rushikeshvartak]

Saviynt uses HQL not SQL

[nmuzinic]

I'm aware of it, it doesn't help in any case

[rushikeshvartak]

Yes you need to raise enhancement