Flexible search


Lets understand some basic concepts about flexible search in Hybris with few examples


It’s a hybris built-in query language based on SQL syntax.

It enables us to search the recordsfrom the database using item types.

In flexible search queries, we never use database table names.

We always use item types which will be mapped to a corresponding tables by Hybris.

Flexible search query execution has 2 phases

1) Pre-parsing phase
In this phase, Hybris converts the flexible search query into SQL query.

2) Executing the SQL converted query
In this phase,converted SQL query will be executed by Hybris.

Note:

In Flexible search query, we should specify the types and attributes within curly braces.

Examples :

  1. Select * from {Product}
Select * from {Product}

This query retrieves all the columns and rows of a Product item type.

The item type specified in the curly braces is the exact item type code defined in the items.xml.

This should have been mapped to specific table using deployment tag in items.xml

for more details on how tables and item types are related, click here and here

  1. SELECT {name[de]}, {name[en]} FROM {Product}
SELECT {name[de]}, {name[en]} FROM {Product}

This query retrieves name of a Product in German and English locale

  1. SELECT {p.code} FROM {Product AS p} ORDER BY {p.code}
SELECT {p.code} FROM {Product AS p} ORDER BY {p.code}

This query retrieves Product code from Product type using alias with the name “p” for Product type.

We can specify the join between 2 types as below

  1. SELECT * FROM {Product JOIN Category}
SELECT * FROM {Product JOIN Category}

This query makes the Join between Product and Category item type.

Note:

We can make different types of Joins like Left outer join,Right outer join and Inner join.

Sub Types

When we search any type in flexible search, by default its subtypes will also be retrieved in the result.

Example:

  1. Select * from {Product}
Select * from {Product}

This will retrieve the instances of Product and VariantProduct.

Since VariantProduct is a sub type of Product, it retrieves its sub types as well.

If we want to exclude the subtypes in the result, we must use exclamation mark (!) while specifying type as below

  1. Select * from {Product!}
Select * from {Product!}

In this example, we have used ! for product type and hence it retrieves only Product instances but not its variants.

Using conditions

  1. SELECT * FROM {Product} WHERE {code} LIKE '%001%'
SELECT * FROM {Product} WHERE {code} LIKE '%001%'

Retrieves all the products whose code has 001 in it.

  1. SELECT * FROM {Product} WHERE {code} LIKE '%001%' AND {code} LIKE '%m%'
SELECT * FROM {Product} WHERE {code} LIKE '%001%' AND {code} LIKE '%m%'

Retrieves all the products whose code has 001 and m in it.

  1. SELECT * FROM {Product} WHERE {code} NOT LIKE '%001%'
SELECT * FROM {Product} WHERE {code} NOT LIKE '%001%'

Retrieves all the products whose code does not contain 001 in it.

  1. SELECT {code},{pk} FROM  {Product} ORDER BY {code} DESC
SELECT {code},{pk} FROM  {Product} ORDER BY {code} DESC

Retrieves the result and Sorts the search results based on the codecolumn in the database in descending order

Runtime Parameters

We can specify run time parameters in flexible search query using placeholder(?) prefix to a specific column.

  1. SELECT {p:pk} FROM {Product AS p} WHERE {p:code} LIKE ?code
SELECT {p:pk} FROM {Product AS p} WHERE {p:code} LIKE ?code

Here we have specified ?code which means run time parameter is code whose value will be added atrun time using query params HashMap.

Executing flexible search queries using API

  1. final Map<String, Object> params = new HashMap<String, Object>();
  2. String query =”SELECT {p:pk} FROM {Product AS p} WHERE {p:code} LIKE ?code”
  3. params.put("code",”001”);
  4. final SearchResult<ProductModel> searchResult = flexibleSearchService.search(query, params);
final Map<String, Object> params = new HashMap<String, Object>();
String query =”SELECT {p:pk} FROM {Product AS p} WHERE {p:code} LIKE ?code”
params.put("code",”001”);
final SearchResult<ProductModel> searchResult = flexibleSearchService.search(query, params);

We are passing run time parameters in the HashMap and passing them to the search method.

Note:

We can pass any number of runtime parameters in the query param using HashMap.
flexibleSearchService object has to be injected by Spring

About the Author

Raj
Founder of javainsimpleway.com
I love Java and open source technologies and very much passionate about software development.
I like to share my knowledge with others especially on technology 🙂
I have given all the examples as simple as possible to understand for the beginners.
All the code posted on my blog is developed,compiled and tested in my development environment.
If you find any mistakes or bugs, Please drop an email to kb.knowledge.sharing@gmail.com

Connect with me on Facebook for more updates

Share this article on