Property based searches in the catalog system

  • Article

While fulltext search allows you to search for phrases which return results that match their meaning, the catalog system also provides another form of search which allows you to query one or more catalogs for results that exactly match the search criteria. Typical examples include

  • Return all the books in the Books catalog where publisher = 'My publisher'.
  • Return all products in a catalog belonging to the books product definition
  • Return all the products in all the catalogs priced less than 8 dollars.

The CatalogSearch class in the Runtime BCLs contains a SqlWhereClause property that allows you to specify the search condition, that you want the results for. This search condition can be any valid SQL where condition as long as the properties that you specify in the search condition exist in the catalogs you are searching. You can also sort the results by specifying a comma separated list of properties to the CatalogSearchOptions.SortProperty property and control the list of properties in the resultant dataset by using the CatalogSearchOptions.PropertiesToReturn property. Typical use cases for the SqlWhereClause property are

  • catalogSearch.SqlWhereClause = "[cy_list_price] < 8";
  • catalogSearch.SqlWhereClause = "[DefinitionName] =N'Book'";
  • catalogSearch.SqlWhereClause = "[DefinitionName] =N'Book' AND ([publisher] = N'Wrox' OR [publisher] = N'Orca') AND  [cy_list_price] < 8";

In addition to the SqlWhereClause you can also control the expected search results by specifying the CatalogClassTypes property. For eg to return all the regular products and product variants belonging to the definition Book and having a price less than 8 dollars, you can use:

CatalogContext catalogContext = CommerceContext.Current.CatalogSystem;

CatalogSearchOptions catalogSearchOptions = new CatalogSearchOptions();

catalogSearchOptions.ClassTypes = CatalogClassTypes.ProductClass |

                                                        CatalogClassTypes.ProductVariantClass ;

CatalogSearch catalogSearch = new CatalogSearch(catalogContext);

      catalogSearch.SearchOptions = catalogSearchOptions;

      catalogSearch.SqlWhereClause="[DefinitionName]=N'Book' AND [cy_list_price]<10";

  • When using property names you should surround the property names with [], to avoid search errors. For eg catalogSearchOptions.PropertiesToReturn = N“[Product Description]“ ; catalogSearch.SqlWhereClause = "[DefinitionName]=N'Book' AND [cy_list_price] < 10";
  • When using string values for performing searches the string values should be preceded by N to ensure that searches on unicode content return expected results. For eg catalogSearch.SqlWhereClause = "[DefinitionName]=N'Book' AND [Publisher] < N'Wrox'";
  • You can use the Classtypes property to control whether a combination of categories, products, product families or product variants is returned by the search. For eg. catalogSearchOptions.ClassTypes = CatalogClassTypes.ProductClass | CatalogClassTypes.CategoryClass | CatalogClassTypes.ProductFamilyClass | CatalogClassTypes.ProductFamilyForVariantClass. If you do not specify the  Classtypes property then all the catalog items matching the search condition will be returned.
  • Any errors during the search will also be logged to the event log on the catalog server.

Finally, you can combine freetext search and property search on one or more catalogs by specifying the catalogSearch.FreeTextSearchPhrase and catalogSearch.SqlWhereClause properties. See this link for a code sample.