Flexible Search Overview

​

FlexibleSearch is a powerful query language used in SAP Hybris Commerce to retrieve data from the database. It provides a flexible and customizable way to perform searches, allowing developers and administrators to query and retrieve specific data based on various criteria. Here's an overview of FlexibleSearch in SAP Commerce:

Key Characteristics of Flexible Search:

​

1) SQL-Like Syntax: FlexibleSearch uses a syntax similar to SQL (Structured Query Language), making it familiar to developers who have experience with SQL.

2) Database-Agnostic: It is database-agnostic, meaning it can work with different types of databases (e.g., MySQL, Oracle, SAP HANA) that SAP Commerce supports.

3) Dynamic Queries: FlexibleSearch allows dynamic queries, where query parameters can be set at runtime. This flexibility is valuable for creating dynamic and adaptable queries.

4) Relational Data: It supports querying across related items and attributes, making it suitable for complex data retrieval scenarios.

5) Customization: Developers can write custom FlexibleSearch queries to suit specific business needs, including searching for products, customers, orders, and more.

 

 

Flexible Search Execution Phase

​

Flexible search query have two phases as discussed below:

​

1) Pre-parsing to SQL: In this phase of query execution first query is changed from flexible query to SQL compliant query. The query conversion depends on the database to which Hybris is connected. 

2) Execution: The SQL complaint query will now be executed to the connected DB. 

​

Flexible Query Syntax and attributes:  The basic syntax of a Flexible Search query looks like this:

 <selects> FROM <types> WHERE <conditions>

•  <selects> parameter for the SELECT clause.

• <types> parameter for the FROM clause.

• Optional <conditions> field for the WHERE clause.

​

​

​

​

Flexible Search Query Type

 

 

 

Here are some common types of Flexible Search query types in Hybris with examples for each:

​

1) Basic Search:

 

SELECT {pk} FROM {Product} WHERE {code} = '12345'

​

2) Advanced Search with Multiple Conditions:

 

SELECT {pk} FROM {Product} WHERE {name} LIKE '%shirt%' AND {price} < 50

​

3) Joins and Relations:

SELECT {p.pk} FROM {Product AS p JOIN Category AS c ON {p:categories} = {c:pk}} WHERE {c:code} = 'clothing'

 

4) Aggregation Functions (e.g., COUNT, SUM, AVG):

 SELECT COUNT({pk}) FROM {Product}

​

5) Sorting:
 

SELECT {pk} FROM {Product} WHERE {price} < 100 ORDER BY {price} DESC

 

6) Grouping and HAVING:

SELECT {c.code}, COUNT({pk}) FROM {Product AS p JOIN Category AS c ON {p:categories} = {c:pk}} GROUP BY {c.code} HAVING COUNT({pk}) > 10

 

7) Parameterized Queries:
 

SELECT {pk} FROM {Product} WHERE {name} = ?productName

​

8) Subqueries:

SELECT {pk} FROM {Product} WHERE {code} IN (SELECT {productCode} FROM {Promotion})

 

9) Fetching Specific Attributes:

​

SELECT {name} FROM {Product} WHERE {code} = '12345'

 

10) Pagination:

​

SELECT {pk} FROM {Product} LIMIT 10 OFFSET 20

​

11) Searching for Null or Not Null Values:

​

SELECT {pk} FROM {Product} WHERE {description} IS NULL

 

12) Union Queries:

​

(SELECT {pk} FROM {Product} WHERE {name} LIKE '%shirt%') UNION (SELECT {pk} FROM {Product} WHERE {name} LIKE '%pants%')

 

13) Conditional Statements (CASE):

​

SELECT {pk}, CASE WHEN {price} > 50 THEN 'Expensive' ELSE 'Affordable' END AS {priceCategory} FROM {Product}

​

14) Dynamic Queries (Using Scripting):

​

SELECT {pk} FROM {Product} WHERE {name} = '{{myVar}}'

​

​

​

​

Flexible Search Execution Modes

​

​

Flexible Search execution from HAC: We can execute the flexible search from HAC by following steps : 

​

1) Log into HAC

2) Click on Console > Open Flexible Search tab

​

​

​

​

​

​

​

​

​

​

​

​

3) Execute your Flexible Search Code

4) Open “SQL Query” to see the  raw SQL code

 

 

 

 

 

 

 

 

​

​

​

​

​

6) Go to the Search result to see the results

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

​

Flexible Search execution using  Search API : We can execute the flexible by java code  in following steps. 

​

1) Inject  FlexibleSearchService in the Dao Class : You'll need to inject the FlexibleSearchService into your Dao class. This can be done using Spring's @Autowired or @Resource annotation.​

2) Create a FlexibleSearchQuery: Create an instance of Flexible Search Query and set the query string.

3) Execute the Query: Use the FlexibleSearchService to execute the query.

4) Process the Results: You can now work with the results from the query. The SearchResult object contains the matched items.

​

1. import de.hybris.platform.servicelayer.search.FlexibleSearchQuery;

2. import de.hybris.platform.servicelayer.search.FlexibleSearchService;

3. import de.hybris.platform.servicelayer.search.SearchResult;

4. import java.util.List;

5. Public Class CustomProductDao 

6. {

7. @Autowired

8. private FlexibleSearchService flexibleSearchService;

9. public List<ProductModel> findProductsByName(String name)

10. {

11.     String queryString = "SELECT {pk} FROM {Product} WHERE {name} LIKE ?name";

12.     FlexibleSearchQuery query = new FlexibleSearchQuery(queryString);

13.     query.addQueryParameter("name", "%" + name + "%");

14.    SearchResult<ProductModel> result = flexibleSearchService.search(query);

15.     return result.getResult();

16. }

17. }