Using an ItemCollection field in flexible search

[Former Member]

Hi,

I'm facing an issue with using a collection type field in my flexible search queries.

• I have defined a PrincipalCollection like this

• I have added that collection to my item type

             <attribute qualifier="assignedPrincipals" type="MyPrincipalCollection">
                   <description>Principal collection</description>
                   <modifiers read="true" write="true" search="true"
                       optional="true" />
                   <persistence type="property" />
               </attribute>
  
  
  

• Now when creating my DAO I have a query that looks like this

  SELECT {item:pk} FROM {MyItem as item} WHERE ?session.user IN ({item:assignedPrincipals})

Now the problem is that this query never returns anything (yes, I was able to add principals to that item and can see them in HMC). The field in the flexible search console query result looks like this

 ,#1,8796093186052,8796093218820,

Any help would be appreciated!

Thanks

Erik

[Former Member · Accepted solution]

Found the answer, worked with users and not principals:

 select * from 
 {MyItem as item JOIN User as pr
 ON 
 {item:assignedUsers} LIKE CONCAT('%',CONCAT({pr.pk},'%'))
 } 
 where (?session.user) = {pr.pk}

[Former Member]

I tried to follow the suggestion over here: https://wiki.hybris.com/display/release5/FlexibleSearch+Tips+and+Tricks

For me to have the collection element type have a reference to the item that hold the collection was not an option. So I went for the second suggestion:

 select * from 
 {MyItem as item JOIN Principal as pr
 ON 
 {item:assignedPrincipals} LIKE CONCAT('%',CONCAT({pr.pk},'%'))
 } 
 where (?session.user) = {pr.pk}

However I get this error:

 column number mismatch detected in rows of UNION, INTERSECT, EXCEPT, or VALUES operation