Logical representation of indexed columns

Multiple indexed columns can be defined for each catalog attribute in the WebSphere Commerce database. Depending on the usage, you can search against different columns. For example, for the Manufacturer Name attribute, two different fields are defined, mfName and mfName_ntk_cs. For faceting in the storefront, you can search against the non-tokenized field, mfName_ntk_cs, but for ranking purposes, you can search against the tokenized field, mfName.

To hide the physical column names from the logic in the business rules and storefront, a utility method retrieves the physical column name given a logical name and its usage. This mapping is stored in the SRCHATTR and SRCHATTRPROP tables, and the utility to retrieve this mapping is defined in the SolrSearchConfigurationRegistry class.

The index supports three types of attributes:
  1. A list of predefined attributes is bootstrapped by default. This is the same list of attributes that are defined in the schema.xml file after the catalog is indexed.
  2. Attribute Dictionary attributes. An event is raised when an attribute is marked as searchable in the Management Center. A listener is registered to the event framework to listen to this event. The listener populates the ATTRDICTSRCHCONF table and the SRCHATTR and SRCHATTRPROP tables. The listener prefixes the ATTR.IDENTIFIER column value with _cat. and uses the prefixed name as the logical name for the attribute. In addition, it populates the usage mappings of this attribute by using a predefined template.
  3. The SRCHATTR and SRCHATTRPROP tables must be manually populated to facet or filter products based on attributes.
Important: When an Attribute Dictionary attribute is deleted from the HCL Commerce database, you must clean up the search facets configuration data in the SRCHATTR and SRCHATTRPROP tables. Otherwise, when you re-create an Attribute Dictionary attribute with the same name, it is not correctly indexed, and as a result, no facet appears in the storefront.

Predefined default attributes for the CatalogEntry search index

The following table lists the attributes that are defined by default, with the Solr indexed column name in brackets for each property:
Predefined default attributes for the CatalogEntry search index
Logical Name (Identifier) Sort Facet Rank Search Display Filter catalogFilter
_cat.CatalogEntry ✓

(catentry_id)
_cat.Member ✓

(member_id)
_cat.ManufacturerName ✓

(mfName_ntk_cs)

 ✓

(mfName_ntk_cs)

 ✓

(mfName)

 ✓

(mfName)

 ✓

(mfName)

 ✓

(mfName)

 ✓

(mfName_ntk_cs)
_cat.PartNumber ✓

(partNumber_ntk)

 ✓

(partNumber_ntk)

 ✓

(partNumber_ntk)

 ✓

(partNumber_ntk)

 ✓

(partNumber_ntk)
_cat.ManufacturerPartNumber ✓

(mfPartNumber_ntk)

 ✓

(mfPartNumber_ntk)

 ✓

(mfPartNumber_ntk)

 ✓

(mfPartNumber_ntk)

 ✓

(mfPartNumber_ntk)
_cat.CatalogEntryType ✓

(catenttype_id_ntk_cs)

 ✓

(catenttype_id_ntk_cs)
_cat.Name ✓

(name_ntk)

 ✓

(name)

 ✓

(name)

 ✓

(name)

 ✓

(name)
_cat.ShortDescription ✓

(shortDescription)

 ✓

(shortDescription)

 ✓

(shortDescription)
_cat.Thumbnail ✓

(thumbnail)
_cat.FullImage ✓

(fullimage)
_cat.Keyword ✓

(keyword)
_cat.OfferPrice_locale ✓

(price_locale)

 ✓

(price_locale)

 ✓

(price_locale)

 ✓

(price_locale)

 ✓

(price_locale)
_cat.Store ✓

(storeent_id)
_cat.ParentCatalogGroup ✓

(parentCatgroup_id_facet)

 ✓

(parentCatgroup_id_search)

 ✓

(parentCatgroup_id_search)

 ✓

(parentCatgroup_id_search)
_cat.ParentCatalogEntry ✓

(parentCatentry_id)

 ✓

(parentCatentry_id)
_cat.ProductSet ✓

(productset_id)
Where:
  • The following properties might exist for each attribute:
    Sort
    Used by the sorting criteria in the Recommend Catalog Entry action.
    Facet
    Used by the storefront to build the facet.
    Rank
    Used by the search filters in search rules to influence the relevancy score.
    Search
    Used by the storefront.
    Display
    Used by the storefront.
    Filter
    Used by the search filter in the Recommend Catalog Entry action and by catalog filter.
    catalogFilter
    Used by catalog filter.
  • The offer price locale represents one of the following values:
    • USD
    • EUR
    • JPY
    • KRW
    • BRL
    • CNY
    • TWD
    • CAD
    • PLN
    • RON
    • RUB
    • EGP
    • GBP

Predefined default attributes for the CatalogGroup search index

The following table lists the attributes that are defined by default:
Predefined default attributes for the CatalogGroup search index
Identifier Property Name Property Value
_cat.EndDate search enddate
_cat.EndDate filter enddate
_cat.StartDate search startdate
_cat.StartDate filter startdate
_cat.CategoryPathName search categoryname
_cat.ParentCatalogGroup facet parentCatgroup_id_facet
_cat.ParentCatalogGroup display parentCatgroup_id_facet
_cat.Store storeent_id storeent_id
_cat.Keyword keyword keyword
_cat.FullImage display fullimage
_cat.Thumbnail display thumbnail
_cat.LongDescription search longDescription
_cat.LongDescription display longDescription
_cat.LongDescription filter longDescription
_cat.ShortDescription search shortDescription
_cat.ShortDescription display shortDescription
_cat.ShortDescription filter shortDescription
_cat.Name sort name
_cat.Name rank name
_cat.Name search name
_cat.Name display name
_cat.Name filter name
_cat.Identifier rank identifier_ntk
_cat.Identifier search identifier_ntk
_cat.Identifier display identifier_ntk
_cat.Identifier filter identifier_ntk
_cat.Identifier catalogFilter identifier_ntk
_cat.Member display member_id
_cat.CatalogGroup display catgroup_id
_cat.OfferPrice_GBP facet price_GBP:{* 50} 50;{50 100} 100;{100 150} 150;{150 200} 200;{200 250} 250;{250 *}
_cat.OfferPrice_EGP facet price_EGP:{* 500} 500;{500 1000} 1000;{1000 1500} 1500;{1500 2000} 2000;{2000 2500} 2500;{2500 *}
_cat.OfferPrice_RUB facet price_RUB:{* 3000} 3000;{3000 6000} 6000;{6000 9000} 9000;{9000 12000} 12000;{12000 15000} 15000;{15000 *}
_cat.OfferPrice_RON facet price_RON:{* 300} 300;{300 600} 600;{600 900} 900;{900 1200} 1200;{1200 1500} 1500;{1500 *}
_cat.OfferPrice_PLN facet price_PLN:{* 300} 300;{300 600} 600;{600 900} 900;{900 1200} 1200;{1200 1500} 1500;{1500 *}
_cat.OfferPrice_CAD facet price_CAD:{* 100} 100;{100 200} 200;{200 300} 300;{300 400} 400;{400 500} 500;{500 *}
_cat.OfferPrice_TWD facet price_TWD:{* 3000} 3000;{3000 6000} 6000;{6000 9000} 9000;{9000 12000} 12000;{12000 15000} 15000;{15000 *}
_cat.OfferPrice_CNY facet price_CNY:{* 1000} 1000;{1000 2000} 2000;{2000 3000} 3000;{3000 4000} 4000;{4000 5000} 5000;{5000 *}
_cat.OfferPrice_BRL facet price_BRL:{* 200} 200;{200 400} 400;{400 600} 600;{600 800} 800;{800 1000} 1000;{1000 *}
_cat.OfferPrice_KRW facet price_KRW:{* 100000} 100000;{100000 200000} 200000;{200000 300000} 300000;{300000 400000} 400000;{400000 500000} 500000;{500000 *}
_cat.OfferPrice_JPY facet price_JPY:{* 10000} 10000;{10000 20000} 20000;{20000 30000} 30000;{30000 40000} 40000;{40000 50000} 50000;{50000 *}
_cat.OfferPrice_EUR facet price_EUR:{* 100} 100;{100 200} 200;{200 300} 300;{300 400} 400;{400 500} 500;{500 *}
_cat.OfferPrice_USD facet price_USD:{* 100} 100;{100 200} 200;{200 300} 300;{300 400} 400;{400 500} 500;{500 *}
_cat.ProductSet search productset_id
_cat.ParentCatalogEntry search parentCatentry_id
_cat.ParentCatalogEntry display parentCatentry_id
_cat.ParentCatalogGroup facet parentCatgroup_id_search
_cat.ParentCatalogGroup rank parentCatgroup_id_search
_cat.ParentCatalogGroup search parentCatgroup_id_search
_cat.ParentCatalogGroup filter parentCatgroup_id_search
_cat.Store search storeent_id
_cat.OfferPrice sort price_USD
_cat.OfferPrice rank price_USD
_cat.OfferPrice display price_USD
_cat.OfferPrice filter price_USD
_cat.OfferPrice catalogFilter price_USD
_cat.OfferPrice merchandising-Sort-Numeric price_USD
_cat.OfferPrice merchandising-FacetExclusion price_USD
_cat.Keyword search keyword
_cat.FullImage display fullimage
_cat.Thumbnail display thumbnail
_cat.ShortDescription search shortDescription
_cat.ShortDescription display shortDescription
_cat.ShortDescription filter shortDescription
_cat.ShortDescription merchandising-Filter-AnyText shortDescription
_cat.ShortDescription merchandising-Rank-AnyText shortDescription
_cat.Name sort name_ntk
_cat.Name rank name
_cat.Name search name
_cat.Name display name
_cat.Name filter name
_cat.Name merchandising-Filter-AnyText name
_cat.Name merchandising-Rank-AnyText name
_cat.Name merchandising-Sort-Text name_ntk
_cat.CatalogEntryType search catenttype_id_ntk_cs
_cat.CatalogEntryType filter catenttype_id_ntk_cs
_cat.ManufacturerPartNumber rank mfPartNumber_ntk
_cat.ManufacturerPartNumber search mfPartNumber_ntk
_cat.ManufacturerPartNumber display mfPartNumber_ntk
_cat.ManufacturerPartNumber filter mfPartNumber_ntk
_cat.ManufacturerPartNumber catalogFilter mfPartNumber_ntk
_cat.ManufacturerPartNumber merchandising-Filter-ExactText mfPartNumber_ntk
_cat.ManufacturerPartNumber merchandising-Rank-ExactText mfPartNumber_ntk
_cat.PartNumber rank partNumber_ntk
_cat.PartNumber search partNumber_ntk
_cat.PartNumber display partNumber_ntk
_cat.PartNumber filter partNumber_ntk
_cat.PartNumber catalogFilter partNumber_ntk
_cat.PartNumber merchandising-Filter-ExactText partNumber_ntk
_cat.PartNumber merchandising-Rank-ExactText partNumber_ntk
_cat.ManufacturerName sort mfName_ntk_cs
_cat.ManufacturerName facet mfName_ntk_cs
_cat.ManufacturerName rank mfName
_cat.ManufacturerName search mfName
_cat.ManufacturerName display mfName
_cat.ManufacturerName filter mfName_ntk
_cat.ManufacturerName catalogFilter mfName_ntk_cs
_cat.ManufacturerName merchandising-Filter-ExactText mfName_ntk
_cat.ManufacturerName merchandising-Rank-ExactText mfName_ntk
_cat.ManufacturerName merchandising-Facet-ExactText mfName_ntk
_cat.ManufacturerName merchandising-Sort-Text mfName_ntk
_cat.Member display member_id
_cat.CatalogEntry display catentry_id