How to add flexible search restriction for Product with custom session attribute and resolve all exceptions

 https://clutcher.github.io/post/hybris/flexible_search_restriction_with_custom_session_attributes/

OOTB hybris has a powerful “Flexible Search Restriction” system, which allows transparently restrict access to any hybris item type. Due to flexible search restrictions are implemented on a very low level of hybris ORM time to time unexpected issues can appear even for simple restrictions.

For example simple task: show products for users only in case if product and users root B2BUnit have relation to same CxSegment.

To implement it you need:

1. Create relation between CxSegment and B2BUnit/Product:

       <relation code="CxSegmentToProductRelation" autocreate="true" generate="true" localized="false">
           <deployment table="CxSegToProdRel" typecode="10310"/>
           <sourceElement qualifier="cxSegments" type="CxSegment" cardinality="many"/>
           <targetElement qualifier="products" type="Product" cardinality="many" collectiontype="set"/>
       </relation>
       <relation code="CxSegmentToB2BUnitRelation" autocreate="true" generate="true" localized="false">
           <deployment table="CxSegToUnitRel" typecode="10311"/>
           <sourceElement qualifier="cxSegments" type="CxSegment" cardinality="many"/>
           <targetElement qualifier="b2bUnits" type="B2BUnit" cardinality="many" collectiontype="set"/>
       </relation>

Copy

2. Create flexible search restriction with query:

EXISTS({{select {stp.pk} from {CxSegmentToProductRelation as stp JOIN CxSegment as s ON {stp.source} = {s.pk}} where {s.code} IN (?session.userSegmen