- Posts: 12
- Joined: Wed Sep 02, 2015 6:53 pm
We have a database table with several fields we are searching through Thunderstone with the SOAP interface.
One of the fields, named list_typ, contains a list of values that are delimited with semi-colon, like this: "02;03;08;10;11".
We want to allow users to select one or more values to search for in this field (for example, find any rows containing 8 or 10) in addition to their keyword query, if they want.
On the Search Settings for this profile, I have this in the Parametric Search Query: list_typ LIKE $qpList_Typ
I have a problem whenever I submit a soap request that includes more than one parameter for this field, like this:
The system searches against '08' just fine, but ignores the '10' in the search, and displays this message about it:
Skipping additional values for qpList_Typ: Previous operator/token `like' may not support multiple values.
Is there another way to accomplish this? Do we need different delimiters in the source data field, or use a different operator in the SQL?
- Site Admin
- Posts: 5504
- Joined: Tue Apr 25, 2000 6:56 pm
- Site Admin
- Posts: 1265
- Joined: Tue Apr 25, 2000 1:27 pm
LIKE should not be used in this case for several reasons:
1) It does not support multiple query values (as noted)
2) With a non-language query like `123', it will match substrings, so the query `123' will match the value `012345' too
3) It may match a subset of the value, e.g. query `foo' will match value `foo bar'
First change list_typ to a Set value so that its values can be properly delimited. You'll also have to change its Data from Field rule so that REX Search is `[^;]+': this will populate the set with the semicolon-delimited values.
Then use INTERSECT in the Parametric Search Query:
list_typ INTERSECT $qpList_Typ IS NOT EMPTY
This will match if any of the $qpList_Typ values are in list_typ, and will do exact (char-for-char) matches of each value.