Introduced in Feature Pack 2

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

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