Making the expansion of search term associations case-sensitive

Search term association expansion is not case-sensitive by default. We can make the expansion case-sensitive by adding a column to the SRCHTERM table and modifying an attribute in the Solr schema.xml file of each core.

Task info

The DSL query /SearchTermAssociation[AssociationType= and (SearchTerms=)]+IBM_Admin_Summary, defined in wc-query-SearchTermAssociation-get.tpl, is used when the marketing services get the synonyms for a search term. It is also used when the SearchDisplay command determines whether a landing page corresponds to a search term. This query by default is not case-sensitive. It is recommended that a generated UPPERCASE column is added to the SRCHTERM table to correspond to the TERM column. Doing so improves the performance of the search term association query in WebSphere Commerce.

Procedure

1. Ensure that you have a customized catalog component configuration file (wc-component.xml). To create this file, follow the steps in the Changing properties in the component configuration file (wc-component.xml) topic.

2. Ensure that you have a customized search extension directory (com.ibm.commerce.search-ext).

   Then, copy the wc-query-SearchTermAssociation-get.tpl file from the com.ibm.commerce.search directory to the com.ibm.commerce.search-ext directory.

3. In the com.ibm.commerce.search-ext/wc-query-SearchTermAssociation-get.tpl file, find the following snippet:

   BEGIN_XPATH_TO_SQL_STATEMENT
           name=/SearchTermAssociation[AssociationType= and (SearchTerms=)]+IBM_Admin_Summary
           base_table=SRCHTERMASSOC
           className=com.ibm.commerce.search.facade.server.services.dataaccess.db.jdbc.SearchTermAssociationSQLComposer
           dbtype=db2
           sql=
                SELECT
                   SRCHTERMASSOC.$COLS:SRCHTERMASSOC$,
               SRCHTERM.$COLS:SRCHTERM$
                FROM
                   SRCHTERMASSOC LEFT OUTER JOIN SRCHTERM ON SRCHTERM.SRCHTERMASSOC_ID = SRCHTERMASSOC.SRCHTERMASSOC_ID
                WHERE
                   SRCHTERMASSOC.STOREENT_ID in ($STOREPATH:catalog$)AND
                   SRCHTERMASSOC.LANGUAGE_ID in ($CONTROL:LANGUAGES$) AND
                   SRCHTERMASSOC.ASSOCIATIONTYPE = ?AssociationType? AND
                   EXISTS (SELECT
                                           1
                                   FROM
                                           SRCHTERM SRCHTERM2
                                   WHERE
                                           SRCHTERM2.SRCHTERMASSOC_ID = SRCHTERMASSOC.SRCHTERMASSOC_ID AND
                                           UPPER(SRCHTERM2.TERM) = UPPER(CAST(?SearchTerms? AS VARCHAR(254))) AND
                                           SRCHTERM2.TYPE = 1
                              )
           dbtype=any
           sql=
                SELECT
                   SRCHTERMASSOC.$COLS:SRCHTERMASSOC$,
               SRCHTERM.$COLS:SRCHTERM$
                FROM
                   SRCHTERMASSOC LEFT OUTER JOIN SRCHTERM ON SRCHTERM.SRCHTERMASSOC_ID = SRCHTERMASSOC.SRCHTERMASSOC_ID
                WHERE
                   SRCHTERMASSOC.STOREENT_ID in ($STOREPATH:catalog$)AND
                   SRCHTERMASSOC.LANGUAGE_ID in ($CONTROL:LANGUAGES$) AND
                   SRCHTERMASSOC.ASSOCIATIONTYPE = ?AssociationType? AND
                   EXISTS (SELECT
                                           1
                                   FROM
                                           SRCHTERM SRCHTERM2
                                   WHERE
                                           SRCHTERM2.SRCHTERMASSOC_ID = SRCHTERMASSOC.SRCHTERMASSOC_ID AND
                                           UPPER(SRCHTERM2.TERM) = UPPER(?SearchTerms?) AND
                                           SRCHTERM2.TYPE = 1
                              )
   END_XPATH_TO_SQL_STATEMENT

   Replace it with the following snippet, which removes the use of UPPER:

   BEGIN_XPATH_TO_SQL_STATEMENT
           name=/SearchTermAssociation[(AssociationType=) and (SearchTerms=)]+IBM_Admin_Summary
           base_table=SRCHTERMASSOC
           className=com.ibm.commerce.search.facade.server.services.dataaccess.db.jdbc.SearchTermAssociationSQLComposer
           dbtype=db2
           sql=
                SELECT
                   SRCHTERMASSOC.$COLS:SRCHTERMASSOC$,
               SRCHTERM.$COLS:SRCHTERM$
                FROM
                   SRCHTERMASSOC LEFT OUTER JOIN SRCHTERM ON SRCHTERM.SRCHTERMASSOC_ID = SRCHTERMASSOC.SRCHTERMASSOC_ID
                WHERE
                   SRCHTERMASSOC.STOREENT_ID in ($STOREPATH:catalog$)AND
                   SRCHTERMASSOC.LANGUAGE_ID in ($CONTROL:LANGUAGES$) AND
                   SRCHTERMASSOC.ASSOCIATIONTYPE in (?AssociationType?) AND
                   EXISTS (SELECT
                                           1
                                   FROM
                                           SRCHTERM SRCHTERM2
                                   WHERE
                                           SRCHTERM2.SRCHTERMASSOC_ID = SRCHTERMASSOC.SRCHTERMASSOC_ID AND
                                           SRCHTERM2.TERM = CAST(?SearchTerms? AS VARCHAR(254)) AND
                                           SRCHTERM2.TYPE = 1
                              )
           dbtype=any
           sql=
                SELECT
                   SRCHTERMASSOC.$COLS:SRCHTERMASSOC$,
               SRCHTERM.$COLS:SRCHTERM$
                FROM
                   SRCHTERMASSOC LEFT OUTER JOIN SRCHTERM ON SRCHTERM.SRCHTERMASSOC_ID = SRCHTERMASSOC.SRCHTERMASSOC_ID
                WHERE
                   SRCHTERMASSOC.STOREENT_ID in ($STOREPATH:catalog$)AND
                   SRCHTERMASSOC.LANGUAGE_ID in ($CONTROL:LANGUAGES$) AND
                   SRCHTERMASSOC.ASSOCIATIONTYPE in (?AssociationType?) AND
                   EXISTS (SELECT
                                           1
                                   FROM
                                           SRCHTERM SRCHTERM2
                                   WHERE
                                           SRCHTERM2.SRCHTERMASSOC_ID = SRCHTERMASSOC.SRCHTERMASSOC_ID AND
                                           SRCHTERM2.TERM = (?SearchTerms?) AND
                                           SRCHTERM2.TYPE = 1
                              )
   END_XPATH_TO_SQL_STATEMENT

4. In our custom extended wc-component.xml file, find the following snippet:

   <_config:property name="CaseSensitive" value="true"/>

   Replace it with the following snippet, setting the value to false:

   <_config:property name="CaseSensitive" value="false"/>

5. Modify the ignoreCase attribute to be false for the solr.SynonymFilterFactory configuration of the wc_text and wc_textSpell fieldTypes in the Solr schema.xml of each core:

   <filter class="solr.SynonymFilterFactory" synonyms="synonyms.txt" ignoreCase="false" expand="true"/> 

6. Restart the WebSphere Commerce server.

Related concepts
Search term association handling

Related tasks
Handling search term associations