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.

<selects>

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.

<types>
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

<conditions>

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%'

<order>

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}"

finalSearchResult<ProductModel>searchResult=flexibleSearchService.search(query);

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"/>

</bean>

Java Class:

public class MyProductService implements ProductService

{

...

private FlexibleSearchService flexibleSearchService;

@Required

public void setFlexibleSearchService(final FlexibleSearchService

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 =

flexibleSearchService.search(query);

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

13 comments:

  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.

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

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

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

    ReplyDelete
  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:
    1.catalog
    2.base store
    3.site
    Looking forward for the answer

    ReplyDelete
  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.

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

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

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

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

    ReplyDelete
    Replies
    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

      Delete
  9. what is code and pk in hybris?

    ReplyDelete
  10. How to get the only date from the date and time in flexible search query? Please help me

    ReplyDelete