Not able to run in condition in a collection

[Former Member]

Hi ,

I have created a Enum as below.

     <enumtype code="EnumType" autocreate="true" generate="true" dynamic="true">
         <value code="E1" />
         <value code="E2" />
     </enumtype>

Then I have created a collection using the same.

 <collectiontype code="EnumTypeList"  elementtype="EnumType"  autocreate="true"    generate="true" type="list" />

I have assigned the collection as an attribute of a item:

 <itemtype code="NewItem" extends="Product" generate="true" jaloonly="false" autocreate="true" jaloclass="package.jalo.NewItem">
             <deployment table="NewItem" typecode="13301" />
             <attributes>
             <attribute qualifier="enums" type="EnumTypeList"
                 generate="true" autocreate="true">
                 <persistence type="property" />
                 <modifiers read="true" write="true" search="true" optional="true" />
             </attribute>    
             </attributes>
 </itemtype>

Now I am trying to run a query on the collection field . My intention is to check if any entries in this collection match to any entry of a list.

 select {a.code} from {NewItem as ni},{EnumType as et} where {a.code}='000091' and {et.pk} in ({ni.brands}) and {et.pk} in (?parameterList)

But this query is throwing an error as invalid number. Please help me with the same. I am completely stuck in it.

[former_member6861]

Hi Vivek, We can write FlexibleSearch query on collection type as well. I have given an example. Please check. http://answers.sap.com/answers/12791044/view.html

exact query for your example will be : SELECT {nt.code} FROM {NewItem as nt join EnumType as et on {nt.enums} like CONCAT('%', {et.pk}, '%')} WHERE {et.code}='E1'

here we can add IN clause

[Former Member]

Vivek have you found a solution for this? I'm facing a similar problem. I'm trying to create a SearchRestriction for a type. But that type is almost exclusively used within a collectiontype. So when the collectiontype is loaded the SearchRestriction is circumvented.

I'm considering changing this from a collectiontype to a one to many relation ship, but that would mean extensive changes (and possibly manual changes) on the data model in the DB.

[andyfletcher]

You can't really search collections with flexible search. IIRC your enums (or brands?) field in the db is actually stored as a serialised ArrayList of Strings.

You'd be better off modeling this as a many to many relationship with a join table if you want to search it.

[Former Member]

Can you provide more information so that I can reproduce?