Flexible Search

The Flexible Search is hybris built retrieval language. It is most powerful data retrieval language from database. It is used for searching or retrieval items and type using SQL-based query. It is simple and similar to SQL queries. In Hybris, we always work on Items and type which are mapped to database table. when we are retrieve items or type from database. we will use flexible search for get item models. Hybris have capability to connect any database like SQL server, Oracle ,HQL etc. Sql query syntax will be vary DB to DB. But Flexible Search framework help to same flexible search query will auto convert according to Database

Flexible Search Execution

The Flexible Search execution has taken in two phases:

Pre-parsing : In first phase, Hybris convert flexible search query into an SQL-compliant statement and resolves the Flexible Search syntax. Flexible Search framework convert flexible query according to connected database. No need to update flexible search query if Project is switching database.
Execution Query: In second phase, Converted SQL Query will execute on the Database.

Basic Syntax :

The basic syntax of a Flexible Search query looks like this:

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

Mandatory <selects> parameter for the SELECT clause.
Mandatory <types> parameter for the FROM clause.
Optional <conditions> field for the WHERE clause.
Optional ORDER BY clause.

Example :

SELECT * FROM {User}

SELECT * FROM {User} WHERE {uuid} ='test'

SELECT * FROM {User} WHERE {uuid} ='test' Order by {name}

SELECT * FROM {User} Order by {name}

This query returns every database column from the User table.

Localized Value: Below query returns the database columns pk , code , and the German localized entries of the name column [de] from the Product table.

select {pk}, {code}, {name[de]} from {Product}

When we write Flexible Search query like below

Select {code} from {Product}

then Flexible Search will convert SQL statement like below that results from the flexible Search query, executed on database:

SELECT item_t0.code FROM products item_t0 WHERE (item_t0.TypePkString IN (?,?,?,?,?,?) );

Syntax

ORDER BY {attribute} : Display results ordered by the value of attribute. default is ascending order

SELECT ... FROM ... ORDER BY...

Select {code} from {Product} ORDER BY {code}

Ascending order : ASC

SELECT ... FROM ... ORDER BY... ASC

Example:

Select {code} from {Product} ORDER BY {code} ASC

Descending order: DESC

SELECT ... FROM ... ORDER BY... DESC

Example:

Select {code} from {Product} ORDER BY {code} DESC

Eliminates double entries : DISTINCT

SELECT DISTINCT ... FROM ...

Example:

Select DISTINCT {name} from {Product}

Logical operations: OR , AND , IS NULL , IS NOT NULL , IN, NOT IN, EXISTS, NOT EXISTS, LIKE

OR: The OR operator displays a record if any of the conditions separated by OR is TRUE.
AND: The AND operator displays a record if all the conditions separated by AND are TRUE.
IS [NOT] NULL: Return result if condition attribute is [not] null.
[NOT] IN : The IN operator allows you to specify multiple values in a WHERE clause. Return result if condition [not] part of given values/
[NOT] EXISTS : Returns the results that are [not] matching a given subquery.
LIKE : Returns the results with Compares to a pattern.

SELECT ... FROM ... WHERE ... OR ...

SELECT ... FROM ... WHERE ... AND ...

SELECT ... FROM ... WHERE ... IS NULL

SELECT ... FROM ... WHERE ... IS NOT NULL

SELECT ... FROM ... WHERE ... IN ...

SELECT ... FROM ... WHERE ... NOT IN ...

SELECT ... FROM ... WHERE ...EXISTS ( {{ SELECT ... }} )

SELECT ... FROM ... WHERE ... NOT EXISTS ( {{ SELECT ... }} )

SELECT ... FROM ... WHERE ... LIKE '...'

Wildcard matching : It any number of characters.

SELECT ... FROM... WHERE ... LIKE '%...'||'...%...'||'...%'

Alias: You can specify an alias to be used for distinguishing attribute fields, using the AS operator:

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

JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

1) INNER JOIN : Merges two tables into one. It is default inner Join. The INNER JOIN keyword selects records that have matching values in both tables.

... JOIN ... ON ... = ...

SELECT * FROM {Category JOIN Catalog}

Example : Join Order , Customer and Address where Order code is 123456

SELECT {o.code}, {c.name}, {a.phone} FROM {Order as o JOIN Customer as c ON {c.pk} = {o.user} JOIN Address as a ON {o.deliveryaddress} = {a.pk} } WHERE {o.code} = '12346'

2) OUTER JOIN : In an outer join, unmatched rows in one or both tables can be returned. It could be LEFT or RIGHT JOIN

... LEFT JOIN ... ON ... = ...

...LEFT OUTER JOIN... ON ... = ...

Example

SELECT {p.pk } FROM {PointOfService AS p LEFT OUTER JOIN Address AS a ON {p.address} = {a.pk}}

WHERE {p.modifiedtime} >= '2020-10-01'

GROUP BY and HAVING CLAUSE

The GROUP BY statement groups rows that have the same values into summary rows and HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.

SELECT {code}, {catalogversion}, MIN(pk) FROM {CMSMedia} GROUP BY {code}, {catalogversion}

HAVING COUNT(1) > 1

Union

The UNION operator is used to combine the result-set of two or more SELECT statements.

SELECT x.description, x.text

FROM ({{

SELECT {r:description[en]} description, 'End result' text FROM {ScriptExecutionResult AS r}

WHERE {r:canBeRunnedAgain} = 0

}} UNION ALL {{

SELECT {rr:description[en]}, 'Retry result' text FROM {ScriptExecutionResult AS rr}

WHERE {rr:canBeRunnedAgain} = 1

}}) x

Exclamation mark ( ! )

By default, specifying a type to search causes a Flexible Search query to search that type and any subtypes. For example, the following code snippet returns the codes and the PKs of all instances of Product and VariantProduct. By adding a trailing exclamation mark ( ! ), the FlexibleSearch query searches only the specified type and omits all subtypes. For example, the following code snippet searches only instances of Product , not of VariantProduct :

SELECT {code},{pk} FROM {Product!}

Passing Parameters

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

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

Testing FlexibleSearch Queries Using the hybris Administration Console

Log into HAC
Go to Console > Flexible Search
Past your Flexible Search Code
Run it
Then go the the Tab “SQL Query” where you will find the raw SQL code

Execute flexible search queries using API

To call a Flexible Search 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>

Java class need to write where we will call flexible Search query.

public class DefaultCustomService implements CustomService

{

...

private FlexibleSearchService flexibleSearchService;

@Required

public void setFlexibleSearchService(final FlexibleSearchService flexibleSearchService)

{

this.flexibleSearchService = flexibleSearchService;

}

...

@Override

public List<ProductModel> getAllProduct() {

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

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

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

return result;

}

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. The Collection returned by SearchResult.getResult() uses the lazy translation approach

Runtime Parameters

we have specified ?code which means run time parameter is code whose value will be added at run time using query params HashMap. We are passing run time parameters in the HashMap and passing them to the search method.

String DETAIL_QUERY = "SELECT {PK} FROM {Product} WHERE {code}=?code";

HashMap<String, Object> params = new HashMap<>();

params.put("code", "0198444");

ProductModel result = null;

FlexibleSearchQuery flexibleSearchQuery = new FlexibleSearchQuery(DETAIL_QUERY, params);

try {

result = flexibleSearchService.search(flexibleSearchQuery);

} catch (ModelNotFoundException mnte) {

}