Logical representation of indexed columns

Multiple indexed columns can be defined for each catalog attribute in the WebSphere Commerce database. Depending on the usage, we 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, we can search against the non-tokenized field, mfName_ntk_cs, but for ranking purposes, we 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 WebSphere Commerce database, we 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					X (catentry_id)
_cat.Member					X (member_id)
_cat.ManufacturerName	X (mfName_ntk_cs)	X (mfName_ntk_cs)	X (mfName)	X (mfName)	X (mfName)	X (mfName)	X (mfName_ntk_cs)
_cat.PartNumber			X (partNumber_ntk)	X (partNumber_ntk)	X (partNumber_ntk)	X (partNumber_ntk)	X (partNumber_ntk)
_cat.ManufacturerPartNumber			X (mfPartNumber_ntk)	X (mfPartNumber_ntk)	X (mfPartNumber_ntk)	X (mfPartNumber_ntk)	X (mfPartNumber_ntk)
_cat.CatalogEntryType				X (catenttype_id_ntk_cs)		X (catenttype_id_ntk_cs)
_cat.Name	X (name_ntk)		X (name)	X (name)	X (name)	X (name)
_cat.ShortDescription				X (shortDescription)	X (shortDescription)	X (shortDescription)
_cat.Thumbnail					X (thumbnail)
_cat.FullImage					X (fullimage)
_cat.Keyword				X (keyword)
_cat.OfferPrice_locale	X (price_locale)		X (price_locale)		X (price_locale)	X (price_locale)	X (price_locale)
_cat.Store				X (storeent_id)
_cat.ParentCatalogGroup		X (parentCatgroup_id_facet)	X (parentCatgroup_id_search)	X (parentCatgroup_id_search)		X (parentCatgroup_id_search)
_cat.ParentCatalogEntry				X (parentCatentry_id)	X (parentCatentry_id)
_cat.ProductSet				X (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