5.4. Coordinated and Joined Searching

Interchange will do a complete range of tests on individual columns in the database. To use this function, set mv_coordinate to Yes (co=yes in the one-click syntax). In order to use coordinated searching, the number of search fields must equal the number of search strings.

To make sure that is the case, use the mv_search_map variable. It allows variables to be mapped to others in the search specification. For example:

   <INPUT TYPE=hidden NAME=mv_search_map VALUE="
       mv_searchspec=search1
       mv_searchspec=search2
       mv_searchspec=search3
       ">
   <INPUT TYPE=hidden NAME=mv_search_field VALUE=title>
   <INPUT TYPE=hidden NAME=mv_search_field VALUE=artist>
   <INPUT TYPE=hidden NAME=mv_search_field VALUE=category>
   Artist: <INPUT NAME=search1 VALUE="">
   Title:  <INPUT NAME=search2 VALUE="">
   Genre:  <INPUT NAME=search3 VALUE="">

Even if the user leaves one blank, the search will work.

Leading/trailing whitespace is stripped from all lines in the mv_search_map variable, so it can be positioned as shown for convenience.

Coordinated searches may be joined with the output of another table if set one of the mv_search_field values is set to a table:column pair. Note that this will slow down large searches considerably unless there is another search specification, as the database must be accessed for every search line If there is a search field that qualifies for a regular expression search function, or conducting a binary search with mv_dict_look, or are not doing an OR search, the penalty should not be too great as only matching lines will cause an access to the database.

Individual field operations can then be specified with the mv_column_op (or op) parameter. The operations include:

   operation            string     numeric   equivalent
   ---------
   equal to               eq         ==           =
   not equal              ne         !=           <>
   greater than           gt         >
   less than              lt         <
   less than/equal to     le         <=
   greater than/equal to  ge         >=
   regular expression     rm                       =~ , LIKE
   regular expression NOT rn                       !~
   exact match            em
   Text::Query::Advanced  aq
   Text::Query::Simple    tq

An example:

   [page search="
           co=yes
           sf=title
           se=Sunflowers
           op=em
           sf=artist
           se=Van Gogh
           op=rm
   "] Sunflowers, Van Gogh </a>

   [page search="
           co=yes

           sf=title
           se=Sunflowers
           nu=0
           op=!~

           sf=artist
           se=Van Gogh
           op=rm
           nu=0

           sf=inventory:qty
           se=1
           op=>=
           nu=1
   "] Any in stock except Sunflowers, Van Gogh </a>

Note that in the second example, nu=0 must be specified even though that is the default. This is to set the proper correspondence. To avoid having to do this, use Interchange's option array feature:

   [page search.0="
                   sf=title
                   se=Sunflowers
                   op=!~
               "
         search.1="
                   sf=artist
                   se=Van Gogh
               "
         search.2="
                   sf=inventory:qty
                   se=1
                   op=>=
                   nu=1
               "
       ] Any in stock except Sunflowers, Van Gogh </a>

The co=yes is assumed when specifying a multiple search.

The second search will check the stock status of the painting provided there is an inventory table as in some of the Interchange demo catalogs. If the qty field is greater than or equal to 1, the product will be picked. If out of stock, it will not be found.

It always helps to have an rm type included in the search. This is used to pre-screen records so that database accesses only need be made for already-matching entries. If accesses must be made for every record, large searches can get quite slow.

The special aq and tq query types only operate if the Text::Query CPAN module is installed. This allows Altavista-style searches on the field, using AND, OR, NOT, and NEAR with arbitrarily complex parentheses.

A useful form for the aq type would be:

  <form action="[area search]" method=POST>
  <input type=hidden name=mv_session_id value="[data session id]">
  <input type=hidden name=mv_column_op VALUE="aq">
  <input type=hidden name=mv_coordinate VALUE=1>
  <input type=hidden name=mv_min_string value=2>
  <input type=hidden name=mv_search_field VALUE=":sku:description:comment:category">
  <input type=hidden name=mv_searchtype VALUE=db>
  <input name=mv_searchspec type=text size=12>
  <input type=submit value="SEARCH">
  </form>

This searches the sku, description, comment, and category fields in the default products file with Text::Query syntax. Try the term "painters NEAR set" in the default foundation example.