Flexible Search in SAP Commerce [Hybris]

In this article, I have explained in details about Flexible Search in SAP Commerce Cloud [SAP Hybris] with some real time examples.

What is Flexible Search in SAP Hybris?

Flexible Search is a Hybris built-in query language based on SQL syntax and it enables us to search records from the database using item types.

• In flexible search queries we never use database table names directly, instead we use item types which are mapped to corresponding tables in the items.xml file.

• In flexible search query we must specify item type code and its attributes in curly braces:            e.g.  Select {code}, {name} from {Product}.

When we execute the flexible search query in Hybris, it has 2 phases:

1. Pre-parsing phase:                                                                                                                                   In this phase, Hybris converts the flexible query into equivalent SQL query.
2. Executing the SQL converted query:                                                                                                     In this phase, the converted SQL query will be executed by Hybris.

Flexible Search Query Examples:

Below are the some of the examples for flexible search queries in Hybris,

Select * from {Product}

Select {code}, {name[en]} from {Product} where {code} = ‘123456’

Select {c.uid}, {c.name[en]} from {Customer as c}

Here, the flexible query retrieves the uid, name using alias name ‘c’ for the item type Customer.

Sub Types in Flexible Search Query Results:

• In Hybris, when we execute any flexible search query, by default it retrieves all the records including its sub-types. For example, if we run below flexible search query:                                   Select * from {Product} - It fetches all records from Product and its sub-type Variant Product.
• If we want to exclude sub types in the search result then we must specify exclamation(!) explicitly as show below:

Select * from {Product!}

Here, we used exclamation mark explicitly, hence it fetches records only from Product item type and excludes the records from its sub-type called Variant Product.

Joins in Flexible Search Query:

• In Flexible Search Query we can specify JOIN to retrieve the records from more than one item types.

Select * from {Country JOIN Region}

In this example, we used JOIN to retrieve records from both Country and Region types.

Note: We use JOIN’s when there is a relation between the item types. In the above example there is a one-to-many relation between Country and Region types.

Flexible Search Query example for one-to-many relation:

Select {c.isoCode} Country_Code, {c.name} Country_Name, {r.name} Region_Name from {Country as c JOIN Region as r ON {r.country} = {c.pk}} where {c.isoCode} = 'US'

 Flexible Search Query example for many-to-many relation:

Select {c.code}, {c.name}, {p.code}, {p.name}  from {Category as c JOIN CategoryProductRelation as rel ON {rel.source}={c.pk} JOIN Product as p ON {rel.target}={p.pk}} where {code} = '1360'

Flexible Search Query with Conditions:

Select * from {Product} where {code} = ‘123456’

In this example, it fetches the record where the code is equal to 123456. 

Select * from {Employee} where {name} LIKE ‘%john%’ Order By {name} ASC

It fetches all records whose name contains ‘john’ and in ascending order.

Select * from {Country as c Join Region as r on {region.coutry} = {c.pk}} where {c.isoCode} = ‘IN’ and {r.name} LIKE %A%

It fetches all records whose country is ‘IN’ and region name contains ‘A’ letter.

Runtime Parameters in Flexible Search Queries:

We can specify runtime parameters using placeholder (?) prefix to a specific parameter as shown below:

Select {p.pk} from {Product as p} where {p.code} = ?code

Here we specified runtime query parameter ‘?code’ and pass the value at runtime as shown below:

final Map<String, Object> queryParams = new HashMap<String, Object>(); String fQuery =”SELECT {p.pk} FROM {Product as p} WHERE {p.code} = ?code” queryParams.put("code",”123456”); final SearchResult<ProductModel> searchResult = flexibleSearchService.search(query, queryParams); List< ProductModel > productList = searchResult.getResult();

If you have any questions or need further clarification, feel free to reach out to me at rameshvanka8@gmail.com. I’m happy to help and would love to hear your feedback!  

Happy Learning😊

Ramesh V