Tuesday, 22 March 2016

Flexible Search In Hybris

Flexible search: (Through hac)
The Flexible Search is a powerful retrieval language built in the hybris Commerce Suite. It enables searching for hybris Commerce Suite types and items using an SQL-based syntax.

Basic Syntax:

The basic syntax of a FlexibleSearch query looks like this:

SELECT <selects> FROM <types> (WHERE <conditions>)? (ORDER BY <order>)?

A FlexibleSearch query consists of:
1. The mandatory <selects> parameter for the SELECT clause.

2. The mandatory <types> parameter for the FROM clause.

3. An optional <conditions> field for the WHERE clause.

4. An optional ORDER BY clause.

Note: all the Commands works like SQL commands.


This field specify the database columns to be returned. The asterisk (*) returns all database columns, as by SQL convention. To search for an attribute, specify the attribute identifier in curly braces, such as: 

SELECT {code} FROM {Product}

Ex: SELECT * FROM {Category} ---> returns every database column from the Category table.

SELECT {pk},{code},{name[de]} FROM {Product} ---> returns the database columns pk , code , and the German localized entries of the name column [de]  from the Product table.

The values for the <types> field in the FROM clause specify the hybris Commerce Suite types, nested in curly braces {and} which are to be searched, such as:

Ex: SELECT * FROM {Product}

SELECT * FROM {Category JOIN Catalog}

Searching Subtypes

SELECT {code}, {pk} FROM {Product} --> returns the codes and the PKs of all instances of Product and VariantProduct

 To omit the subtypes: use ! Mark in the command

SELECT {code}, {pk} FROM {Product!} ---> Searches only instances of Product, not of VariantProduct


The values for the <conditions> field in the optional WHERE clause narrow down the number of matches by specifying at least one condition that is matched by all search results.

Ex: SELECT * FROM {Product} WHERE {code} LIKE '%al%'


The FlexibleSearch complies with the SQL syntax in terms of ordering results. By specifying an attribute in an ORDER BY clause, the list of search results are sorted according to the specified type.

Ex: SELECT {code}, {pk} FROM {Product} ORDER BY {code} DESC ---> sorts the search results by the values of the code database column, in descending order

The execution of a Flexible Search statement takes place in two phases:
1. pre-parsing into an SQL-compliant statement

2. Running that statement on the database.

Eg: To access a type in a FlexibleSearch query, surround the type code with curly braces {and}, as in:

SELECT * FROM {Product}

For example, the following two code snippets show a flexible Search query and the statement that results from the flexible Search query, which is executed on the database:

Eg:FlexibleSearch query:

Select {code} from {Product}

SQL statement that results from the flexible Search query, executed on database:

SELECT item_t0.code FROM products item_t0 WHERE (item_t0.TypePkString IN

(?,?,?,?,?,?) );

Flexible search using Hybris Commerce suite API:

FlexibleSearch queries using the hybris Commerce Suite API can be done in two
steps, both of which can be done in one Java statement:

1. Setting up the query

2. Running the query.

Constructing a FlexibleSearch Query:
A FlexibleSearch query is constructed as a string which contains the query,

such as:

final String query = "SELECT {pk} FROM {Product}"


Ex: Java Code

1. final String query = "SELECT {" + ProductModel.PK + "} FROM {" +

ProductModel._TYPECODE + "}";

2. String query =

   "SELECT {p:" + ProductModel.PK + "} FROM {" + ProductModel._TYPECODE + "

AS p}\n"+

   "WHERE {" + ProductModel.VARIANTTYPE + "} IS NOT NULL"

Calling a FlexibleSearch:

To call a FlexibleSearch statement using the API use flexibleSearchService,
which is always available through the spring, and has to be properly injected to
your service as follows:

<bean id="myProductService" class="de.hybris.platform.foobar.MyProductService" >

<property name="flexibleSearchService" ref="flexibleSearchService"/>


Java Class:

public class MyProductService implements ProductService



private FlexibleSearchService flexibleSearchService;


public void setFlexibleSearchService(final FlexibleSearchService



this.flexibleSearchService = flexibleSearchService;




The flexibleSearchService search(...) methods returns 

de.hybris.platform.servicelayer.search.SearchResult instance, which holds a

List of the individual search results. To access this List, call

the SearchResult class getResult() method, such as:

final String query = "SELECT {" + ProductModel.PK + "} FROM {" +

ProductModel._TYPECODE + "}";

final SearchResult<ProductModel> searchResult =


List<ProductModel> result = searchResult.getResult();

FlexibleSearch using the hybris Management Console:
Triggering FlexibleSearch queries within the hybris Management Console is possible in two ways:

1. Saved Query instances

2. View Type instances

  • A ViewType instance is the hybris Commerce Suite representation of a database view. 
  • A SavedQuery instance is a means of using a FlexibleSearch query to retrieve items in the hybris Commerce Suite instead of using the GenericSearch


  1. How to exclude specific attributes not to return in Flexible search query?

    for e.g I have product item type which have pk,name,media,price etc.

    if i run select {pk} from {product}, then it should not return media attribute.

    1. i think they mansion this above pls go through the post

    2. select {pk} from {product} will return only PK
      select {pk},{name},{media},{price} from {product} will return pk, name, price and media.

  2. use this Select {pk} from {Product!} in flexible search console you will find only PKs
    {product!}---> means excluding subtypes

    1. Thanks for the post, I am techno savvy. I believe you hit the nail right on the head. I am highly impressed with your blog. It is very nicely explained. Your article adds best knowledge to our Java Online Training from India. or learn thru Java EE Online Training Students.

  3. Hi everyone,

    My questions is,
    If we want to setup the complete application from the scratch, what is the proper sequence of the following we should follow:
    2.base store
    Looking forward for the answer

  4. catalog->store->cms-content->site->solr->solrtrigger->email this order we are following in our project. Always remember before running localized impexes always run main impexes.

  5. Something about IN operator.
    Is it possible in Hybris in FlexibleSearchService?

  6. what is the use of flexible query in hybris?

  7. Under what circumstances FlexibleSearchService will result in application level error?

  8. Hi ,
    Can anyone explain me how i can fatch Supercategory of the product using Flexible search query

    1. Use category product relation table and join both category and products for products which has hierarchy like supercategory-> Products , else use category product relation , category category relation table for products which has hierarchy like super category -> category -> product