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 new 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, and when the SearchDisplay command determines if a landing page corresponds to a search term. This query by default is not case sensitive. To improve the performance of the search term association query in WebSphere Commerce, it is recommended that a generated UPPERCASE column be added to the SRCHTERM table, to correspond to the TERM column.

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.