Examples of Flexible Search Queries

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'
    }
  • Get all the applied voucher and their orders of 2016. It may not work with relative vouchers
SELECT {v.code} voucherCode, {o.code} orderCode, {o.date} orderDate, {o.TotalDiscounts} orderTotalDiscounts,{d.value} discountValue
FROM {VoucherInvalidation AS vi 
   JOIN Voucher AS v
     ON {vi.voucher} = {v.pk}
   JOIN ORDER AS o
     ON {vi.order} = {o.pk}
     AND {o.plzForCalculation} IS NOT NULL
     AND {o.date} BETWEEN '2016-01-01' AND '2016-12-31 23:59:59'
   JOIN OrderDiscountRelation AS odr
     ON {odr.source} = {o.pk}
    JOIN Discount AS d
     ON {odr.target} = {d.pk} 
 }
 WHERE {vi.status} = 'confirmed'

Outer joins

  • Modified point of services or their address
SELECT {p.pk }
FROM {PointOfService AS p
LEFT OUTER JOIN Address AS a
  ON {p.address} = {a.pk}}
WHERE {p.modifiedtime} >= '2020-10-01'
  OR {a.modifiedtime} >= '2020-10-01'
ORDER BY {p.baseStore}, {p.creationtime} DESC

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 consoleRunning 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 parametersHow to see what parameters where replaced on the query

– Based on Hybris 6.6

Discussion

Enter your comment. Wiki syntax is allowed: