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
/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:
- 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 ofUPPER
: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:
Replace it with the following snippet, setting the value to false:<_config:property name="CaseSensitive" value="true"/>
<_config:property name="CaseSensitive" value="false"/>
- Modify the ignoreCase attribute to be
false
for the solr.SynonymFilterFactory configuration of thewc_text
andwc_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.