Mastering SAP Hybris Flexible Search Query

If you’re working with SAP Commerce, then Flexible Search Query is one of the most powerful tools you’ll use daily. Whether you’re debugging, writing DAOs, or preparing for interviews – this is a must-know concept.

What is Flexible Search Query?

Flexible Search Query is SAP Hybris’s built-in query language used to retrieve data from the Hybris Type System. Instead of writing raw SQL with table and column names, you can easily write queries using type names and attribute names, as defined in items.xml. This means you don’t need to worry about how data is stored internally. In any application, data is stored in a database. To work with that data, we perform CRUD operations:

• C -> Create (Insert data)
• R -> Read (Fetch data)
• U -> Update (Modify data)
• D -> Delete (Remove data)
  

Flexible Search is used only for READ operations in SAP Commerce. For the other operations (Create, Update, and Delete), developers typically use the ModelService or ImpEx (Import/Export).

Why we use Flexible Search Query?

If you’re new to SAP Commerce, then one question you’ll definitely have is, “Why not just use SQL queries?” Why FlexibleSearch?”

Let’s break it down in a simple and practical way:

1. Database Independance: Flexible Search works across multiple databases like MySQL, Oracle, and SAP HANA. You don’t need to modify queries for different DBs. This makes your application portable and easy to maintain.
2. Type aware queries: We write flexible search queries using item types like Product and Order instead of table names. This aligns with the Hybris data model. It removes the need to understand complex database structures.
3. Supports Localized attributes: Flexible Search allows you to fetch localized data like product names or descriptions based on language. You can specify the language directly in the query. This makes handling multi-language data very easy.
4. Built-in security: It respects search restrictions and user permissions automatically. This ensures only authorized data is fetched. Unlike SQL, it does not bypass platform security.
5. Automatic Inheritance: If you search for {Product}, the system is smart enough to also find VariantProduct or any other subtypes you’ve created.

How Flexible Search Query Works:

Flexible search query execution has 2 phases:

Phase	What Happens	The Result
1. Pre-parsing	The system looks for {itemtype} and resolves them using platform metadata.	Converts {itemtype} to the actual DB table name.
2. Execution	The generated SQL is sent to the database	The database runs the query and returns raw data.

Syntax:

The Basic blueprint:

SELECT {attribute} FROM {ItemType AS alias} WHERE {attribute} = value

The FlexibleSearch query syntax is very similar to SQL; however, it comes with a special “curly brace” twist.

Curly braces { }: This is the most important rule. Everything that refers to a Type or an Attribute must be inside curly braces.

Example:

SELECT {pk} FROM {Product}

Attributes and Aliases: In SAP Hybris Flexible Search, attributes and aliases play a key role in writing clear, efficient queries. Attributes represent the fields of an item type and Aliases act as short names for item types. They make queries more readable and help when working with joins or multiple item types.

Example:

SELECT {p.code} FROM {Product AS p} WHERE {p.name} LIKE '%Camera%'

The Exclamation Mark ! (Subtype Filter): By default, {Product} returns products AND variants. If you want only Products then add an !.

• FROM {Product} -> includes variants.
• FROM {Product!} -> include only base products.

Example:

SELECT {pk} FROM {Product!}

Localized atttributes: When you want to fetch language-specific values, just use square brackets.

Example:

SELECT {code}, {name[de]} FROM {Product}

Outer Join Modifier (o): Used when you want to perform a LEFT OUTER JOIN, meaning even if related data is missing, the result should still be returned.

SELECT {p.code}, {c:name:o} FROM {Product AS p JOIN Category AS c ON {p.supercategories} = {c.pk}}

Sub Query ( {{ }}): Subqueries in SAP Hybris Flexible Search support certain use cases, but they come with limitations compared to standard SQL. You can primarily use them in WHERE clauses, especially with IN, EXISTS, and NOT EXISTS. Double curly braces {{ }} are mandatory for subqueries and subquery must return single column.

SELECT {p.code}
FROM {Product AS p}
WHERE {p.pk} IN (
   {{ SELECT {oe.product} 
      FROM {OrderEntry AS oe} }}
)

How to use Flexible Search in Java:

STEP 1: Inject FlexibleSearchService:

In your DAO (Data Access Object), you’ll use the FlexibleSearchService

@Autowired
private FlexibleSearchService flexibleSearchService;

STEP 2: Write your query as string:

Build the query string. Always use ?paramName placeholders – never paste values directly into the string.

final String query = "SELECT {p:pk} FROM {Product AS p} " + "WHERE 
{p:code} = ?code " + "AND {p:catalogVersion} = ?catVer";

STEP 3: Create Flexible Search Query and add parameters:

Wrap your string in a FlexibleSearchQuery object and attach parameter values safely.

final FlexibleSearchQuery fsQuery = new FlexibleSearchQuery(query);
fsQuery.addQueryParameter("code",   "LAPTOP001");
fsQuery.addQueryParameter("catVer", catalogVersion);

STEP 4: Execute and get results:

Call search() to run the query. The result gives you both the list of models and the total count for pagination.

// Execute the query
SearchResult<ProductModel> result =  flexibleSearchService.search(fsQuery);
// Get the list of Product models
List<ProductModel> products = result.getResult();
// Get total count — useful for pagination
int total = result.getTotalCount();

Test in HAC first! Before putting a query in Java code, test it in HAC → Console → FlexibleSearch. In HAC, you can run queries instantly, see results in a table, and view the generated SQL. It’s the fastest way to debug.

Let’s learn through Examples:

1. Basic select query: Return all database columns for product table.

SELECT * FROM {Product}

2. Fetch Specific Attributes: Retrieve name and code of all products

SELECT {code}, {name} FROM {Product}

3. Apply filter (WHERE): Fetch product with code ‘P0001’

SELECT {code}, {name} 
FROM {Product} 
WHERE {code} = 'P0001'

4. Wildcard matching ( LIKE): Fetch products whose name contains ‘shirt’

SELECT {code}, {name} 
FROM {Product} 
WHERE {name} LIKE '%shirt%'

LIKE operator is used to search for a specified pattern in a column.

• % : this sign represents any number of characters, even zero characters.
  Example: name start with ‘a’ (‘a%’), name includes ‘or’ (‘%or%’)
• _ : represent one single character.
  Example: return name whose second letter is ‘l’ (‘_l%’)

5. Sorting (ORDER BY): Fetch products sorted by creation time (latest first)

SELECT {code}, {creationtime} 
FROM {Product}
ORDER BY {creationtime} DESC

ORDER BY is used to sort result set in ascending (ASC) or descending (DESC) order. By default, we get result in ascending order.

6. Localized attribute: Get the English name and German name of all products.

SELECT {code}, {name[en]}, {name[de]} FROM {Product}

7. JOIN: Find all products belonging to a specific Catalog Version (e.g., ‘Online’).

SELECT {p.pk} 
FROM {Product AS p JOIN CatalogVersion AS cv 
ON {p.catalogVersion} = {cv.pk}} 
WHERE {cv.version} = 'Online'

8. JOIN more than 2 tables: Find all products that belong to a category with the code ‘electronics’.

SELECT {p.pk} 
FROM {Product AS p 
JOIN CategoryProductRelation AS rel 
ON {p.pk} = {rel.target} 
JOIN Category AS c 
ON {rel.source} = {c.pk}
} 
WHERE {c.code} = 'electronics'

9. Sub query: Fetch products ordered with quantity greater than 5

SELECT {code}
FROM {Product}
WHERE {pk} IN (
   {{
      SELECT {product}
      FROM {OrderEntry}
      WHERE {quantity} > 5
   }}
)

10. Aggregation with HAVING: Fetch orders having more than 3 entries

SELECT {o.code}, COUNT({e.pk})
FROM {Order AS o 
      JOIN OrderEntry AS oe ON {oe.order} = {o.pk}}
GROUP BY {o.code}
HAVING COUNT({oe.pk}) >= 3

11. Fetch Categories having more than 10 products

SELECT {c:code}, COUNT({rel:target})
FROM {Category AS c 
      JOIN CategoryProductRelation AS rel ON {rel:source} = {c:pk}}
GROUP BY {c:code}
HAVING COUNT({rel:target}) > 10

12. Find out products witch are ordered more than 2 times

SELECT {p:code}, COUNT({e:pk})
FROM {Product AS p 
      JOIN OrderEntry AS e ON {e:product} = {p:pk}}
GROUP BY {p:code}
HAVING COUNT({e:pk}) > 2