Here are some examples of what can be query with FlexibleSearch. Please feel to send me complex queries which could help others understand the syntax.

Inner Joins

  • Get the cms components assigned to a content slot. It doesn't take the catalog version into account.
SELECT {e2s:target}
  FROM {
    contentslot AS s
  JOIN ElementsForSlot AS e2s
    ON {e2s:SOURCE} = {s:pk} }
  WHERE {s:uid} = 'priceChartEvolutionPage_main_slot' 
  • Get all fields of an object of type Player where the isocode of the country of the object equals to "SYR". Player is a subtype of User
SELECT *
  FROM { Player AS p
      JOIN Country AS c
        ON {c:pk} = {p:country}
        AND {c:isocode}='SYR'
    }

Subqueries

  • Get the pk of the warehouses which have at least one plzRange.
SELECT {ware.pk}
FROM {Warehouse AS ware}
  WHERE EXISTS ({{
        SELECT 1
        FROM {PLZRange AS plz}
        WHERE {ware:pk} = {plz:WAREHOUSE}
    }})

Group by

  • Get the pk of the oldest CMSMedia which is duplicated:
SELECT {code}, {catalogversion}, min(pk)
FROM {CMSMedia}
GROUP BY {code}, {catalogversion}
HAVING count(1) > 1

Union

  • Get the list of results adding a text. This is an useless example
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

A select of the result of the union is mandatory.

Localized Attributes

  • Using the square brackets you select value of a localized attribute for the given language
select {t:code}, {t:subject[en]}, {t:subject[de]}
  from {RendererTemplate as t}

Usage of the flexibleSearchService

  • It looks for the deployment environments of the Areco Deployment Manager Extension
@Repository
public class FlexibleSearchDeploymentEnvironmentDAO implements DeploymentEnvironmentDAO {

(...)

/**
* {@inheritDoc }
*/
@Override
public Set<DeploymentEnvironmentModel> loadEnvironments(final Set<String> environmentNames) {
(...)

final Set<String> normalizedEnvironmentNames = new HashSet<String>();
for (final String givenEnvironmentName : environmentNames) {
normalizedEnvironmentNames.add(givenEnvironmentName.trim().toUpperCase(Locale.getDefault()));
}

final StringBuilder queryBuilder = new StringBuilder();
queryBuilder.append("SELECT {r.").append(ItemModel.PK).append("}").append(" FROM {")
.append(DeploymentEnvironmentModel._TYPECODE).append(" as r ").append("} ").append(" WHERE ").append(" UPPER({")
.append(DeploymentEnvironmentModel.NAME).append("}) ").append(" IN ").append('(').append('?')
.append(DeploymentEnvironmentModel.NAME).append(')');

final FlexibleSearchQuery query = new FlexibleSearchQuery(queryBuilder.toString());
query.addQueryParameter(DeploymentEnvironmentModel.NAME, normalizedEnvironmentNames);
final SearchResult<ScriptExecutionResultModel> searchResult = this.flexibleSearchService.search(query);
if (environmentNames.size() != searchResult.getCount()) {
throw new IllegalStateException("Some environments don't exist. Please check that these names are valid: "
+ environmentNames);
}
return new HashSet(searchResult.getResult());
}

(...)

}

Conversion of Flexible Search to SQL

  • 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
  • The question marks must be replaced with the values of the Hybris' type and catalog versions PKs. You can see these values on the tab "Execution statistics"
HAC - Flexible Search console
Running a Flexible Search Query on HAC
See the raw SQL on the flexible console
How to see the raw SQL on the Flexible Search console on HAC
Flexible Search query's replaced parameters
How to see what parameters where replaced on the query

 

Based on Hybris 6.3

Add comment


Security code
Refresh