de.hybris.platform.servicelayer.search

Interface FlexibleSearchService

All Known Implementing Classes:

DefaultFlexibleSearchService

public interface FlexibleSearchService

The FlexibleSearchService is an SQL-based search on item types which allows to search on attribute values as well as directly on database columns. Unlike SQL statements, a FlexibleSearch query can contain references to items in the hybris Platform (products, units, customers, and so on) as parts of the query statement (marked by { and }). Therefore, a FlexibleSearch query is first interpreted (resolving the references) and then executed. During the interpretation phase, the FlexibleSearch framework resolves those references and replaces them with the actual names of columns and tables. This phase also does automatic joins of property tables where necessary.

An overview on the FlexibleSearch syntax:

 <query> = "SELECT" <selects> "FROM" <types> ( "WHERE" <conditions> ( "ORDER BY" <order> )? )?
 <selects> = SQL <i>select</i> expression that contains attribute <field> sections
 <types> = SQL <i>from</i> expression that contains item <type> and <typejoin> sections
 <type> = "{" <single-type-clause> "}" 
 <typejoin> = "{" <single-type-clause> ( ( "LEFT" )? "JOIN" <single-type-clause> ( "ON" <join-conditions> )? )+ "}"
 <single-type-clause> = code of type "!"? ( "AS" <type-alias> )? <type-modifiers>?
 <type-alias> = a unique name of this type inside this query
 <join-conditions> = SQL boolean condition including <field> sections ( "AND" SQL boolean condition including <field> sections )*
 <conditions> = SQL <i>where</i> expression containing attribute <field> sections and <subselect> sections
 <order> = SQL <i>order by </i> expression containing attribute <field> sections
 <field> = "{" ( <type-alias> ":" )? <attribute-name> <language>( ":" <modifiers> )? "}"
 <attribute-name> = the attribute name
 <language> = "[" language PK string + "]"
 <modifiers> = ( "c" | "l")? "o"? ... core field , localized property, outer joined property  
 <subselect> = "{{" <query> "}}"
 

A simple select to find all items of one type can be done like this:

 final String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "}";
 final SearchResult<UnitModel> searchResult = flexibleSearchService.search(query);
 

Please note that items are always referenced as "{" + ItemModel.PK + "}" . The actual item type is defined by the search result signature list.

It is highly recommended to use parametrized queries when using WHERE clauses as follows:

 final String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "} WHERE {" + UnitModel.CODE + "} = ?code ";
 final Map<String, String> queryParams = new HashMap<String, String>();
 queryParams.put("code", "foo");
 
 final SearchResult<UnitModel> searchResult = flexibleSearchService.search(query, queryParams);
 

If you know that a search query is likely to yield a lot of results (such as a search for all products and their variants, for example), we discourage getting all of those search results in a single FlexibleSearch call: the results will be stored in a single Collection, which may result in slow performance. A better way is to split the query into several calls by limiting the number of search results per call. To use this paging mechanism, use the search(...) method with FlexibleSearchQuery object as parameter. You have to set on FlexibleSearchQuery object the setNeedTotal to true. If this parameter is set to true , the FlexibleSearch framework splits the number of returned search results into pages. Via the start and range parameters, you can retrieve pages of search results. The following code snippet, for example, iterates over all search results of the FlexibleSearch query three at a time:

 String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "} ORDER BY " + UnitModel._TYPECODE;
 final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query);
 fQuery.setCount(range);
 fQuery.setNeedTotal(true);
 
 int start = 0;
 final int range = 3;
 int total;
 
 do
 {
        fQuery.setStart(start);
        final SearchResult<UnitModel> searchResult = flexibleSearchService.search(fQuery);
        total = searchResult.getTotalCount();
        start += range;
 }
 while (start < total);
 

FlexibleSearch is able of retrieving raw data as well as items. Furthermore, it is possible to select multiple attributes or columns. The result of multi-attribute selections is a list of lists.

 String query = "SELECT {" + UnitModel.PK + "}, LOWER({" + UnitModel.CODE + "}), {" + UnitModel.NAME + "} FROM {"
                + UnitModel._TYPECODE + "}";
 final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query);
 fQuery.setResultClassList(Arrays.asList(UnitModel.class, String.class, String.class));
 final SearchResult<List<?>> result = flexibleSearchService.search(fQuery);
 final List<List<?>> resultList = result.getResult();
 
 for (final List<?> list : result)
 {
        final UnitModel unit = (UnitModel) list.get(0);
        final String code = (String) list.get(1);
        final String name = (String) list.get(2);
 
        // ....
 }
 

As seen here it is possible to use any column based SQL functions around flexible search sections ( "LOWER({" + UnitModel.CODE + "}" ) since only these sections are replaced. The rest of the query is left untouched. This way even database specific functionality is available (though this might break database independence).

Ordering is as simple as selecting different attributes

 String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "} ORDER BY {" + UnitModel.CODE + "} ASC";
 final SearchResult<UnitModel> searchResult = flexibleSearchService.search(query);
 

A special note on properties: when order by contains properties which are stored in separate tables these tables are outer joined automatically since non existence of the property would avoid the actual item to be found otherwise.

Localized properties require that a locale is specified for searching. Normally search uses the current session locale. FlexibleSearchService automatically will execute query in local session context with passed locale.

 String query = "SELECT {" + UnitModel.PK + "} FROM {" + UnitModel._TYPECODE + "} ORDER BY " + UnitModel._TYPECODE;
 final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query);
 fQuery(new Locale("en");
 
 final SearchResult<UnitModel> searchResult = flexibleSearchService.search(fQuery);
 

Further it is possible to specify a fixed language for each localized attribute - even multiple different languages per query. please note that this generates as many joins to localized property tables as languages are specified, so be careful.

 final LanguageModel en = commonI18NService.getLanguage("en");
 final LanguageModel de = commonI18NService.getLanguage("de");
 final String query = "SELECT {" + LanguageModel.PK + "} FROM {" + LanguageModel._TYPECODE + "} WHERE {name["
                + en.getPk().getLongValueAsString() + "]} = ?en AND {name[" + de.getPk().getLongValueAsString() + "]} = ?de";
 final Map params = new HashMap<String, LanguageModel>();
 params.put("en", "English");
 params.put("de", "Englisch");
 final FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query, params);
 
 final SearchResult<LanguageModel> search = flexibleSearchService.search(fQuery);
 

The fixed languages are passed as language pk strings.

Joins between types are allowed. Currently inner joins and left outer joins are allowed.

 final String query = "SELECT {products:" + ProductModel.PK + "}, {carts:" + CartEntryModel.UNIT + "}, SUM({carts:"
                + CartEntryModel.QUANTITY + "}) AS amount " + "FROM {" + ProductModel._TYPECODE + " AS products JOIN "
                + CartEntryModel._TYPECODE + " AS carts ON {products:" + ProductModel.PK + "} = {carts:" + CartEntryModel.PRODUCT
                + "} } GROUP BY {products:" + ProductModel.PK + "}, {carts:" + CartEntryModel.UNIT + "}";
 FlexibleSearchQuery fQuery = new FlexibleSearchQuery(query);
 fQuery.setResultClassList(Arrays.asList(ProductModel.class, UnitModel.class, Long.class))
 
 final SearchResult> res = flexibleSearchService.search(fQuery);
 final List> result = res.getResult();
 
 for (final List row : result)
 {
        final ProductModel product = (ProductModel) row.get(0);
        final UnitModel unit = (UnitModel) row.get(1);
        final Long quantity = (Long) row.get(2);
 }
 

The above example produces a list of all products which are currently held in carts grouped by unit and show the total quantity. Since this is no left (outer) join all products which are not inside a cart don't show up in the result.

There is a special syntax for subselects too. Please note that subselects are not available in case the underlying database doesn't support them!

 String query = "SELECT {" + ProductModel.PK + "} FROM {" + ProductModel._TYPECODE + " AS p} WHERE " + " EXISTS ( {{" + "SELECT {"
                + OrderEntryModel.PK + "} FROM {" + OrderEntryModel._TYPECODE + "} WHERE {" + OrderEntryModel.PRODUCT + "} = {p:"
                + ProductModel.PK + "} " +
                // be sure to have a space between the last '}' and '}}' - this is a know issue right now                                                                            
                "}} )";
 

Subselects may contain all features as normal queries including joins and nested subselects.

The result of a flexible query is always limited to the queried type, which means result items are instances of the queried type or any of its subtypes. It is possible to select instances of one type exactly by appending a ! to the type code.

 String query = "SELECT {PK} FROM {MyType!}";
 

Since:

4.0

See Also:

FlexibleSearchQuery

Spring Bean ID:

flexibleSearchService

Method Summary

Modifier and Type	Method and Description
<T> T	getModelByExample(T example) Search for an existing object (in the database) by a given (not saved) example.
<T> List<T>	getModelsByExample(T example) Search for an existing objects (in the database) by a given (not saved) example.
<T> SearchResult<T>	search(FlexibleSearchQuery searchQuery) Search.
<T> SearchResult<T>	search(String query) Simplest search available.
<T> SearchResult<T>	search(String query, Map<String,? extends Object> queryParams) Convenience method which internally delegates to search(FlexibleSearchQuery).
<T> SearchResult<T>	searchRelation(ItemModel model, String attribute, int start, int count) This method is not implemented.
<T> SearchResult<T>	searchRelation(RelationQuery query) This method is not implemented.
<T> T	searchUnique(FlexibleSearchQuery searchQuery) Searches for exactly one model fitting given FlexibleSearchQuery.
TranslationResult	translate(FlexibleSearchQuery searchQuery) Translates FlexibleSearchQuery into TranslationResult object which contains translated FlexibleSearchQuery to real SQL query and all parameters which will be used in SQL query.

Method Detail

getModelByExample

<T> T getModelByExample(T example)

Search for an existing object (in the database) by a given (not saved) example. Every modified attribute in this example is used as search parameter.

Type Parameters:

T - the type of the example and the return type

Parameters:

example - the example

Returns:

the result if this was found by the example.

Throws:

ModelNotFoundException - if nothing was found

AmbiguousIdentifierException - if more than one model is found for the given search parameters

getModelsByExample

<T> List<T> getModelsByExample(T example)

Search for an existing objects (in the database) by a given (not saved) example. Every modified attribute in this example is used as search parameter. Returns all matching results.

Type Parameters:

T - the type of the example and the return type

Parameters:

example - the example

Returns:

the list of models found by the example.

search

<T> SearchResult<T> search(FlexibleSearchQuery searchQuery)

Search.

Parameters:

searchQuery - the search query

Returns:

the search result< t>

search

<T> SearchResult<T> search(String query)

Simplest search available.

Parameters:

query - the query

Returns:

the search result< t>

search

<T> SearchResult<T> search(String query,
                           Map<String,? extends Object> queryParams)

Convenience method which internally delegates to search(FlexibleSearchQuery).

Parameters:

query - query string in flexible search syntax

queryParams - additional query parameters; null permitted

Returns:

the search result< t>

searchRelation

<T> SearchResult<T> searchRelation(ItemModel model,
                                   String attribute,
                                   int start,
                                   int count)

This method is not implemented.

searchRelation

<T> SearchResult<T> searchRelation(RelationQuery query)

This method is not implemented.

searchUnique

<T> T searchUnique(FlexibleSearchQuery searchQuery)

Searches for exactly one model fitting given FlexibleSearchQuery. Throws an exception when more or none results found.

Parameters:

searchQuery - the search query

Returns:

the search result< t>

Throws:

ModelNotFoundException - if nothing was found

AmbiguousIdentifierException - if more than one model is found for the given search parameters

translate

TranslationResult translate(FlexibleSearchQuery searchQuery)

Translates FlexibleSearchQuery into TranslationResult object which contains translated FlexibleSearchQuery to real SQL query and all parameters which will be used in SQL query.

Translation also respects SearchRestrictionModel based restrictions for types and users.

Parameters:

searchQuery - FlexibleSearchQuery to be translated.

Returns:

TranslationResult object containing SQL query with parameters.