multiple params for parametric field search from database field

Post Reply
brittonclair
Posts: 12
Joined: Wed Sep 02, 2015 6:53 pm

multiple params for parametric field search from database field

Post by brittonclair »

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:
<ns:Param name="qplist_typ">08</ns:Param>
<ns:Param name="qplist_typ">10</ns:Param>

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?
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

multiple params for parametric field search from database field

Post by mark »

Try
<ns:Param name="qplist_typ">(08,10)</ns:Param>
User avatar
Kai
Site Admin
Posts: 1271
Joined: Tue Apr 25, 2000 1:27 pm

multiple params for parametric field search from database field

Post by Kai »

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.
Post Reply