'the other way' for OperatorId.IN_SET ?

[Len5inG]

Hi,
The OperatorId.IN_SET let me create a Criterio where i can check if a fields value is in the array.

I need the 'opposite'.
I have a field with multiple true and want to get those who contains the search-value within their array-value.

the standard OperatorId.CONTAINS does not fullfil my needs because this could give too much results.

Example:
Record 1: colors: [red, green, light-red]
Record 2: colors: [light-red]

Criterion("colors", OperatorId.CONTAINS, "red")

would also return Record 2.

Is there a possibility to check if an array-value 'contains' one item which equals the serach-value.

[Blama]

Hi Len5inG,

root cause is a non-normalized data model. I think you'll have to work around it:

AdvancedCriteria(OperatorId.OR, {
Criterion("colors", OperatorId.CONTAINS, ", red,"),
Criterion("colors", OperatorId.STARTS_WITH, "red,"),
Criterion("colors", OperatorId.ENDS_WITH, ", red") }
)

Best regards,
Blama

[Len5inG]

Thx for the advice.

The workaround would not handle the very rare case if one value + the separator is a substring of another value:

'red' and 'dark,red' as values.
thus checking for red would result in a true with ends_with ",end".

as mentioned this is a very rare case, but it could be possible.

I think i will use contains with the additional constraint for our database that one value never may be a substring of another value.