Making the expansion of search term associations case-sensitive

Search term association expansion is not case-sensitive by default. You 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.

About this task

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

Ensure that you have a customized catalog component configuration file (wc-component.xml). To create this file, follow the steps in:
- Changing properties in the component configuration file (wc-component.xml) (WC EAR)
- Changing properties in the component configuration file (wc-component.xml) (Search EAR)
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.

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

In your 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"/>
```
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"/> 
```
Restart the WebSphere Commerce server.