Wednesday, August 19, 2026

SAP Commerce FlexibleSearch Explained: Queries, Joins, Parameters, Pagination & Best Practices


Introduction

FlexibleSearch is one of the most frequently used features in SAP Commerce development.

Whether you are developing a DAO, troubleshooting production data, building a CronJob, implementing a Service Layer operation, or preparing for a SAP Commerce interview, you will almost certainly work with FlexibleSearch.

A typical FlexibleSearch query looks like:

SELECT {pk}
FROM {Product}
WHERE {code} = 'PRODUCT-001'

At first glance, FlexibleSearch looks very similar to SQL.

However, there are important differences.

Instead of directly querying database tables and columns, FlexibleSearch primarily works with SAP Commerce item types and attributes.

SAP describes FlexibleSearch as an SQL-based search language for SAP Commerce items. The platform first resolves the FlexibleSearch syntax and then executes the resulting database query. (SAP Help Portal)

What is FlexibleSearch?

FlexibleSearch is SAP Commerce's built-in query language for retrieving data.

It allows developers to query objects such as:

Product
Customer
Order
OrderEntry
Cart
Category
Media
PriceRow
CatalogVersion

Instead of writing:

SELECT *
FROM products
WHERE p_code = 'P001'

you typically write:

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

The major advantage is that the query works with the SAP Commerce Type System rather than requiring developers to directly depend on physical database table names.

FlexibleSearch vs SQL

FlexibleSearchSQL
Uses SAP Commerce item typesUses database tables
Uses SAP Commerce attributesUses database columns
Uses {} syntaxUsually no {}
Understands the Type SystemDatabase-specific
Works through SAP Commerce APIsDirect database access
Supports SAP Commerce-specific featuresStandard database language

For example:

SQL

SELECT p_pk
FROM products
WHERE p_code = 'P001'

FlexibleSearch

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

Basic FlexibleSearch Syntax

The basic structure is:

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

For example:

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

Understanding Curly Braces

One of the most important things to remember is the use of:

{}

For example:

{Product}

represents an item type.

And:

{code}

represents an attribute.

So:

SELECT {code}
FROM {Product}

means:

Select the code attribute from Product items.

Selecting Multiple Attributes

You can select multiple attributes:

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

However, when working in Java, selecting only the data you actually need is often preferable to retrieving unnecessary columns.

Using Aliases

Aliases become extremely useful when working with joins.

Example:

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

Now p represents Product.

You can write:

SELECT {p.code}, {p.name}
FROM {Product AS p}
WHERE {p.code} = 'P001'

WHERE Clause

The WHERE clause filters results.

Example:

SELECT {pk}, {code}
FROM {Product}
WHERE {code} = 'P001'

You can also use:

AND

Example:

SELECT {pk}
FROM {Product}
WHERE {code} = 'P001'
AND {approvalStatus} IS NOT NULL

OR Condition

Example:

SELECT {pk}, {code}
FROM {Product}
WHERE {code} = 'P001'
OR {code} = 'P002'

You can also use:

IN

which is usually cleaner:

SELECT {pk}, {code}
FROM {Product}
WHERE {code} IN ('P001', 'P002', 'P003')

LIKE Operator

FlexibleSearch supports pattern matching.

Example:

SELECT {pk}, {code}
FROM {Product}
WHERE {code} LIKE 'ABC%'

This can find products whose code starts with:

ABC

Another example:

WHERE {name} LIKE '%Laptop%'

NOT Operator

Example:

SELECT {pk}, {code}
FROM {Product}
WHERE {code} NOT LIKE 'TEST%'

ORDER BY

You can sort results:

SELECT {pk}, {code}, {name}
FROM {Product}
ORDER BY {code}

Descending:

ORDER BY {code} DESC

Multiple sorting fields:

ORDER BY {name} ASC, {code} ASC

Parameterized FlexibleSearch

One of the most important best practices is to use parameters.

Avoid:

String query =
        "SELECT {pk} FROM {Product} " +
        "WHERE {code} = '" + productCode + "'";

Instead:

String query =
        "SELECT {pk} FROM {Product} " +
        "WHERE {code} = ?code";

Then:

FlexibleSearchQuery flexibleSearchQuery =
        new FlexibleSearchQuery(query);

flexibleSearchQuery.addQueryParameter(
        "code",
        productCode);

Finally:

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

SAP's API documentation describes FlexibleSearch execution through a FlexibleSearchQuery and the FlexibleSearchService. (SAP Help Portal)

Why Should You Use Parameters?

Parameterized queries provide several advantages:

  • Cleaner code

  • Better separation of query and data

  • Avoid string concatenation

  • Safer handling of values

  • Easier query maintenance

Therefore, prefer:

WHERE {code} = ?code

over dynamically concatenating values into the query string.

FlexibleSearchService

In Java, FlexibleSearch is commonly executed through:

FlexibleSearchService

Example:

@Resource
private FlexibleSearchService flexibleSearchService;

Then:

final String query =
        "SELECT {pk} " +
        "FROM {Product} " +
        "WHERE {code} = ?code";

final FlexibleSearchQuery searchQuery =
        new FlexibleSearchQuery(query);

searchQuery.addQueryParameter(
        "code",
        "P001");

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

Get the result:

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

Complete DAO Example

A typical DAO method might look like:

public ProductModel findProductByCode(
        final String code)
{
    final String query =
            "SELECT {p:pk} " +
            "FROM {Product AS p} " +
            "WHERE {p:code} = ?code";

    final FlexibleSearchQuery searchQuery =
            new FlexibleSearchQuery(query);

    searchQuery.addQueryParameter(
            "code",
            code);

    final SearchResult<ProductModel> result =
            flexibleSearchService.search(searchQuery);

    return result.getResult()
                 .stream()
                 .findFirst()
                 .orElse(null);
}

This is a typical pattern for a SAP Commerce DAO.

FlexibleSearch Joins

Joins are extremely important in real SAP Commerce projects.

For example:

Order
  |
  └── User

We can retrieve orders along with customer information.

Example:

SELECT
    {o.code},
    {u.uid}
FROM
{
    Order AS o
    JOIN User AS u
        ON {o.user} = {u.pk}
}

Product and CatalogVersion Join

A common SAP Commerce query involves products and catalog versions.

SELECT
    {p.code},
    {cv.version}
FROM
{
    Product AS p
    JOIN CatalogVersion AS cv
        ON {p.catalogVersion} = {cv.pk}
}

You can then filter:

WHERE {cv.version} = 'Online'

Product and Category

Relations can also be queried.

Conceptually:

Product
   |
   | supercategories
   |
Category

A query can join the appropriate relation endpoint or related item attributes depending on the specific data model.

Always check the relevant Type System definitions before constructing a complex relation query.

FlexibleSearch with Enum

SAP Commerce frequently uses enumeration values.

For example:

OrderStatus

might contain:

CREATED
PROCESSING
COMPLETED
CANCELLED

Depending on the data model, you can query the corresponding enum value through the appropriate relation/reference.

For example:

SELECT {o.pk}
FROM {Order AS o}
WHERE {o.status} = ?status

In Java:

searchQuery.addQueryParameter(
        "status",
        OrderStatus.CREATED);

Using the model/enum value as a query parameter is generally preferable to hard-coding database-specific identifiers.

Date Queries

Date filtering is very common in CronJobs and reporting.

Example:

SELECT {pk}, {code}, {creationtime}
FROM {Order}
WHERE {creationtime} >= ?startDate
AND {creationtime} <= ?endDate

Java:

searchQuery.addQueryParameter(
        "startDate",
        startDate);

searchQuery.addQueryParameter(
        "endDate",
        endDate);

This is much better than constructing dates directly into the query string.

NULL Checks

To find products where an attribute is not populated:

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

To find populated values:

SELECT {pk}, {code}
FROM {Product}
WHERE {description} IS NOT NULL

COUNT

FlexibleSearch supports aggregate queries.

For example:

SELECT COUNT({pk})
FROM {Product}

This can be useful when you only need a count instead of loading every model.

For large datasets, this can be significantly more appropriate than retrieving thousands of models just to call:

list.size()

GROUP BY

Example:

SELECT
    {catalogVersion},
    COUNT({pk})
FROM {Product}
GROUP BY {catalogVersion}

This can be useful for reporting and analysis.

Subqueries

FlexibleSearch also supports subqueries for applicable scenarios.

A conceptual example is:

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

Subqueries should be used carefully, particularly for large datasets.

Always verify the generated SQL and execution performance for production workloads.

Searching Only a Specific Type

SAP Commerce supports type inheritance.

For example:

Product
   |
   └── VariantProduct

Depending on the query, searching the parent type can include subtypes.

The exact-type modifier can be useful when you specifically want to exclude subtypes.

Example:

SELECT {pk}
FROM {Product!}

The ! is an important FlexibleSearch feature to understand when dealing with type inheritance.

Localized Attributes

SAP Commerce supports localized attributes.

For example, a product name may exist in:

English
German
French

FlexibleSearch can access localized values using the appropriate language information.

For example:

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

The exact language handling depends on the platform language configuration and query requirements.

Pagination

Pagination is extremely important when retrieving large amounts of data.

A basic FlexibleSearchQuery can be configured with:

searchQuery.setStart(0);
searchQuery.setCount(100);
searchQuery.setNeedTotal(true);

Conceptually:

Start = 0
Count = 100

means:

Start from the first result and retrieve up to 100 results.

SAP Commerce also provides PaginatedFlexibleSearchService for pagination and sorting use cases. SAP's documentation describes it as supporting pagination and sorting through SearchPageData. (SAP Help Portal)

PaginatedFlexibleSearchService

For storefront and service-layer pagination scenarios, you may encounter:

PaginatedFlexibleSearchService

It works with objects such as:

SearchPageData
PageableData
SortData

A simplified flow is:

Request
   ↓
PageableData
   ↓
PaginatedFlexibleSearchService
   ↓
FlexibleSearch
   ↓
SearchPageData

This is especially useful when exposing paginated results through a facade/controller layer.

FlexibleSearch in HAC

FlexibleSearch queries can also be executed through the SAP Commerce Administration Console.

For example:

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

This is extremely useful for:

  • Troubleshooting

  • Data validation

  • Production support

  • Development

  • Checking relationships

  • Investigating unexpected data

However, always be careful when running expensive queries against production databases.

FlexibleSearch Performance

Writing a query that works is not enough.

The query also needs to perform well.

For example, avoid:

for (ProductModel product : products)
{
    String query =
        "SELECT {pk} FROM {OrderEntry} " +
        "WHERE {product} = ?product";

    // Execute query
}

This can result in the classic:

N+1 query problem

If there are 10,000 products, you could potentially execute thousands of database queries.

A better approach is often to retrieve the required information using one appropriately designed query or a controlled batching strategy.

Avoid FlexibleSearch Inside Loops

This is one of the most important SAP Commerce performance rules.

Bad:

for (ProductModel product : products)
{
    getOrdersForProduct(product);
}

where:

getOrdersForProduct()

executes FlexibleSearch every time.

Better:

Design a query that retrieves the required data in a single operation, or process data in controlled batches.

Select Only What You Need

Avoid unnecessarily retrieving huge datasets.

Instead of:

SELECT {pk}, {code}, {name}, {description},
       {manufacturerName}, {summary}, ...
FROM {Product}

if you only need:

code

use:

SELECT {code}
FROM {Product}

For large datasets, this can reduce memory and processing overhead.

Read-Only Replica

SAP Commerce Cloud can support FlexibleSearch against a configured read-only data source.

SAP documentation notes that complex queries can be directed to a read-only replica to improve performance of the main data source. (SAP Help Portal)

This is particularly interesting for:

  • Reporting

  • Complex read queries

  • Large data analysis

  • Read-heavy workloads

However, read-only replicas have their own consistency considerations, so they should be used according to the application's requirements.

FlexibleSearch Restrictions

SAP Commerce can apply search restrictions to FlexibleSearch operations depending on the context.

This is another reason FlexibleSearch should not simply be treated as direct SQL.

The platform's security and search restriction mechanisms can influence the results returned.

FlexibleSearch vs ModelService

This is a very common interview question.

FlexibleSearch

Used primarily to:

READ / RETRIEVE

data.

ModelService

Used for:

CREATE
UPDATE
SAVE
REMOVE
REFRESH

models.

Example:

List<ProductModel> products =
        flexibleSearchService.search(query)
                             .getResult();

Then:

product.setName("Updated Name");

modelService.save(product);

So the typical flow is:

FlexibleSearch
      ↓
Retrieve Model
      ↓
Modify Model
      ↓
ModelService.save()

FlexibleSearch vs Direct SQL

A common question is:

Why don't we simply use SQL?

Because SAP Commerce applications are built around the platform's Type System and persistence abstractions.

FlexibleSearch allows developers to query SAP Commerce types and attributes rather than coupling application code directly to database-specific table structures. SAP explicitly describes this Type System-oriented approach in its FlexibleSearch documentation. (SAP Help Portal)

Direct database access should therefore not be the default approach for normal application development.

Common FlexibleSearch Mistakes

1. Forgetting Braces

Wrong:

SELECT pk FROM Product

Typical FlexibleSearch syntax:

SELECT {pk}
FROM {Product}

2. Hard-Coding Values

Avoid:

"WHERE {code} = '" + code + "'"

Prefer:

"WHERE {code} = ?code"

3. Selecting Too Much Data

Don't retrieve thousands of models when you only need a count.

Use:

SELECT COUNT({pk})
FROM {Product}

4. FlexibleSearch Inside Loops

This can create severe performance problems.

Always look for opportunities to combine queries or batch processing.

5. Missing CatalogVersion

When working with catalog-aware types such as Product, remember that the same product code can exist in different catalog versions depending on the data model.

For example:

Product
   |
   +-- Staged
   |
   +-- Online

A query may therefore need to explicitly consider catalog version.

Real-World Example: Find Products in Online Catalog

SELECT
    {p.code},
    {p.name}
FROM
{
    Product AS p
    JOIN CatalogVersion AS cv
        ON {p.catalogVersion} = {cv.pk}
    JOIN Catalog AS c
        ON {cv.catalog} = {c.pk}
}
WHERE {cv.version} = 'Online'

You could further restrict the catalog:

AND {c.id} = 'electronicsProductCatalog'

This is a very common type of query in real SAP Commerce projects.

Real-World Example: Find Orders Created Today

A parameterized query is preferable:

SELECT
    {o.pk},
    {o.code},
    {o.creationtime}
FROM {Order AS o}
WHERE {o.creationtime} >= ?startDate
AND {o.creationtime} < ?endDate

Java:

query.addQueryParameter(
        "startDate",
        startDate);

query.addQueryParameter(
        "endDate",
        endDate);

Using a half-open interval:

>= start
< end

can make date-range handling cleaner and avoid boundary problems.

Real-World Example: Count Orders by User

SELECT
    {o.user},
    COUNT({o.pk})
FROM {Order AS o}
GROUP BY {o.user}

This can be useful for reporting or analysis.

Debugging a Slow FlexibleSearch Query

When a FlexibleSearch query is slow, don't immediately assume FlexibleSearch itself is the problem.

Investigate:

  1. Query complexity

  2. Number of joins

  3. Search restrictions

  4. Database indexes

  5. Number of records

  6. Sorting

  7. Pagination

  8. Execution plan

  9. Large result sets

  10. N+1 query patterns

A query that works perfectly with 1,000 products may behave very differently with millions of records.

FlexibleSearch Best Practices

✅ Use Query Parameters

WHERE {code} = ?code

✅ Retrieve Only Required Data

Don't select unnecessary attributes.

✅ Use Pagination

Especially for large result sets.

✅ Avoid Queries Inside Loops

Look for batch or join-based solutions.

✅ Filter Early

Apply restrictive conditions wherever appropriate.

✅ Understand the Type System

Know the relationships between:

Product
Catalog
CatalogVersion
Category
Order
OrderEntry
Customer

✅ Test Production-Scale Data

A query that works locally is not necessarily production-ready.

Interview Questions

What is FlexibleSearch?

FlexibleSearch is SAP Commerce's SQL-like query language used to retrieve data through the SAP Commerce Type System.

What is FlexibleSearchService?

FlexibleSearchService is the Service Layer API used to execute FlexibleSearch queries.

Why do we use {} in FlexibleSearch?

Curly braces identify SAP Commerce item types and attributes within FlexibleSearch syntax.

Example:

{Product}
{code}
{p.code}

What is the difference between SQL and FlexibleSearch?

SQL operates directly on database tables and columns, while FlexibleSearch works primarily with SAP Commerce item types and attributes and is translated into database-specific SQL.

How do you pass parameters?

Use:

WHERE {code} = ?code

and:

query.addQueryParameter("code", code);

How do you implement pagination?

You can configure FlexibleSearchQuery with start/count settings or use PaginatedFlexibleSearchService for pagination and sorting use cases. (SAP Help Portal)

How do you improve FlexibleSearch performance?

Common approaches include:

  • Avoiding N+1 queries

  • Avoiding FlexibleSearch inside loops

  • Selecting only required data

  • Using appropriate filtering

  • Pagination

  • Reviewing database indexes

  • Reducing unnecessary joins

  • Testing against realistic data volumes

Can FlexibleSearch update data?

FlexibleSearch is primarily used for retrieval. For normal application-level create/update/delete operations, SAP Commerce developers typically use APIs such as ModelService or Impex rather than treating FlexibleSearch as a general-purpose DML language.

Conclusion

FlexibleSearch is one of the most important technologies to master as an SAP Commerce developer.

A strong developer should be comfortable with:

SELECT
WHERE
AND / OR
LIKE
IN
ORDER BY
JOIN
COUNT
GROUP BY
Subqueries
Parameters
Pagination
Localized attributes
Relations

But knowing the syntax is only half the job.

For real-world SAP Commerce development, you also need to understand performance.

The most important rule to remember is:

A FlexibleSearch query that returns the correct result is not necessarily a good production query.

Always consider:

Correctness
   +
Performance
   +
Scalability
   +
Maintainability

That mindset will help you write much better SAP Commerce code.

Posted by at
Labels: , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)