Parametric search support

A parametric search allows queries using multiple criteria to narrow the results. For instance, all products that have a name starting with "Toy", under $50, that have color = "blue". WebSphere Commerce supports parametric search queries on both vertical and horizontal schemas.

In the database, if the search attributes are stored in the horizontal schema, then every different attribute has a specific column where it is stored. For instance, products have a PARTNUMBER column. If, instead, they are stored in the vertical schema then the columns are not specific and different attributes can be stored in every row. For example, product can have two attribute rows associated with it, color = blue, and size = large.

For optimal performance, the searchable attributes must be stored in the horizontal schema tables. These tables may include the base table to store base information about a product such as a part number or brand, or associated tables for storing localized data such as descriptions.

Note:


The search() function

search() is a special function that is part of a WebSphere Commerce extended XPath notation. The function can be used to reduce the number of parametric search queries that a developer needs to create.

The search function can be placed in an XPath expression in the predicate (the string in square brackets in the example below) of the noun. The argument to the search() function, together with the mapping between the logical model SDO objects and database schema are used to generate SQL fragments that get injected into the parametric search query templates.

Note: The search function also supports 'and' and 'or' conditions. However, both 'and' and 'or' cannot be used in the search function simultaneously. In the following example, a parametric search XPath expression queries products that have short description containing the "Polo shirt" string and part numbers prefixed with "FU01":

The attribute mapping for the logical entity CatalogEntry shown below, defines CATENTRY as a base table and the CATENTDESC as an associated table. The ‘Description/ShortDescription‘ element (property name) mapping is searched for in the ‘columns' elements of the base table, CATENTRY. If no match is found, the CATENTDESC associated table is searched.

The XPath to SQL template with a search() function looks as follows:

In the following generated SQL statement, the $ATTR_TBLS$ variable is replaced with the associated table containing the column corresponding to the ‘Description/ShortDescription' property as defined in the mapping above. The $ATTR_CNDS$ variable is replaced with the join condition between this table and the base table and the condition to compare the ‘SHORTDESCRIPTION' column with the short description value provided as input. The condition for the ‘CatalogEntryIdentifier/ExternalIdentifier' property corresponding to the PARTNUMBER column in the base CATENTRY table is also included.

Note: The search() function can only refer to the properties mapped to the columns of the base table representing the logical entity object or the columns of the tables that have foreign key relationships to the base table. For example, the search() function used in the predicate of the 'Catalog' element can refer to the catalog's 'description' property. However, it should not refer to the description property of the catalog entries. Also, the search() function is limited to a single element in the XPath expression. Only the first occurrence of the search() function is considered when generating SQL fragments for generated searches.


Configure parametric search

The component configuration file, wc-component.xml, contains a section for the data service layer. It can be divided into 3 main subsections:

This configuration is described by the <dataservice> element. An example of the configuration is shown in the following XML sample:

The data mediator section defines the following information:

These context definitions are used to retrieve the context data at runtime and substitute them into the template SQL statement. The context information section has the following information:


Attribute mapping

A single logical object has properties stored in multiple physical tables. DSL supports 3 different kinds of tables:

  1. Base table: contains the domain unique identity information. Each column could represent a property of the logical object (the base table typically represents your noun).

  2. Associated table: additional properties stored in a separate table, as part of the horizontal schema. This table has a foreign key relationship with the base table. Each column could represent a property of the logical object.

  3. Property table: additional properties stored in a separate table, as part of the vertical schema. Each row could represent a property/value pair of the logical object. This table has a foreign key relationship with the base table.

The following XML is a sample attribute mapping configuration:

Each mapping element defines a mapping of a logical object to multiple physical tables. The key element identifies the property of the logical model that references the logical entity object.

Element Attribute Description
basetable Identifies the base table
name Name of the table
useAllColumn Specifies whether all columns in the table are used for parametric search. The attribute values are:

  • true - all table columns are used. The logical property name mapping to the column name is based on the object-relational metadata information unless they are explicitly overridden by the columns sub-elements (described later). The useAllColumn attribute is optional. If not specified, this is the default.

  • false - only those columns identified in the columns sub-elements are considered.

columns Sub element of basetable, and associated table that identifies the column information for these tables.
name The name of the column.
propertyname The logical XPath property name, if specified. Otherwise, the property name defined for this column in the object-relational metadata is used.
searchable Specifies if this is used for parametric search.
caseSensitive Specifies whether the searches should treat the value of this column as case-sensitive or not case-sensitive.

  • true - the content of this column is considered case-sensitive. This is the default.

  • false - the value of this column is not case-sensitive. When the caseSensitive attribute is set to false, the UPPER() SQL function is used in the generated SQL statement to change the case of the column content. Users should be aware of the performance impact of using the not case-sensitive searches. The use of the UPPER() function will cause a full index scan or even a table scan. For Oracle users, IBM recommends that we use a function-based index to improve performance.

genmode Specifies the generation mode.
associatedtable Defines the associated table. Attributes of this element are the same as for the basetable element.
propertytable Defines the properties table. Only the name attribute is supported.
columns Sub element of propertytable that identifies the column information for these tables.
name The name of the column.
propertyname The logical model property name.


Generation modes

When search attributes are connected by the logical ‘or' operator, a number of search query generation options can be selected by configuration. Different generation modes allow you to fine-tune the performance of the generated queries. For example, with large data sets, breaking a single search query into multiple queries and combining the result set using the UNION operator may provide the best performance. The following generation modes can be specified using the genmode attribute of the columns element:

Generation mode genmode attribute value
UNION 0
EXISTS 1
IN 2

The UNION mode is the default mode. It is used if no configuration is specified. It is also used if at least one of the search attributes is configured to use the UNION mode. The EXISTS mode is used if one of the search attributes is configured for the EXISTS mode and none are configured for the UNION mode. The IN mode is used if all search attributes are configured to use it.


Overriding the generated SQL for parametric search queries

We can override the generated SQL for parametric search queries. For example, if a certain parametric search is not performing well, the database administrator may suggest another way to write the SQL. In this case, we can completely override the SQL used for the parametric search query.

To override the generated SQL, all you need to do is define a new XPATH_TO_SQL_STATEMENT block in our custom template file. The name of the query must be the XPath key, with the search parameters specified explicitly. This query is used instead of generating the SQL.

Following the sample expression we have been using in this section, the name (and overriding SQL) would be as shown in the following example: