====== 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.
<code sql>
SELECT {e2s:target}
  FROM {
    contentslot AS s
  JOIN ElementsForSlot AS e2s
    ON {e2s:SOURCE} = {s:pk} }
  WHERE {s:uid} = 'priceChartEvolutionPage_main_slot'
</code>
  * 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
<code sql>
SELECT *
  FROM { Player AS p
      JOIN Country AS c
        ON {c:pk} = {p:country}
        AND {c:isocode}='SYR'
    }
</code>
  * Get all the applied voucher and their orders of 2016. It may not work with relative vouchers
<code sql>
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'
</code>
===== Outer joins =====
  * Modified point of services or their address
<code sql>
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
</code>

===== Subqueries =====
  * Get the pk of the warehouses which have at least one plzRange.
<code sql>
SELECT {ware.pk}
FROM {Warehouse AS ware}
  WHERE EXISTS ({{
        SELECT 1
        FROM {PLZRange AS plz}
        WHERE {ware:pk} = {plz:WAREHOUSE}
    }})
</code>

===== Group by =====
  * Get the pk of the oldest CMSMedia which is duplicated:
<code sql>
SELECT {code}, {catalogversion}, min(pk)
FROM {CMSMedia}
GROUP BY {code}, {catalogversion}
HAVING count(1) > 1
</code>

===== Union =====
  * Get the list of results adding a text. This is an useless example
<code sql>
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
</code>

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
<code sql>
select {t:code}, {t:subject[en]}, {t:subject[de]}
  from {RendererTemplate as t}
</code>

====== Usage of the flexibleSearchService ======

  * It looks for the deployment environments of the Areco Deployment Manager Extension
<code java>
@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());
  }
  
  (...)
  
}
</code>

====== 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"

{{ :sap_hybris_commerce:import_export:hacrunflexiblesearchquery.png |HAC - Flexible Search console}}Running a Flexible Search Query on HAC

{{ :sap_hybris_commerce:import_export:hacrunrawsqlqueries.png |See the raw SQL on the flexible console }}How to see the raw SQL on the Flexible Search console on HAC

{{ :sap_hybris_commerce:import_export:hacflexiblesearchseereplacedparameters.png |Flexible Search query's replaced parameters}}How to see what parameters where replaced on the query

-- Based on Hybris 6.6