meta data for this page
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revisionNext revisionBoth sides next revision | ||
sap_hybris_commerce:import_export:examples-of-flexible-search-queries [2019/08/12 16:27] – created Antonio Robirosa | sap_hybris_commerce:import_export:examples-of-flexible-search-queries [2019/08/19 15:02] – [Conversion of Flexible Search to SQL] Antonio Robirosa | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== Examples of Flexible Search Queries ====== | + | ====== 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 ===== |
- | < | + | |
- | <hr id=" | + | |
- | <h2>Inner Joins</h2> | + | <code sql> |
- | <ul> | + | SELECT {e2s: |
- | <li>Get the cms components assigned to a content slot. It doesn' | + | FROM { |
- | </ul> | + | contentslot AS s |
- | <pre class=" | + | JOIN ElementsForSlot AS e2s |
- | | + | ON {e2s: |
- | contentslot | + | WHERE {s:uid} = ' |
- | | + | </code> |
- | | + | |
- | | + | <code sql> |
- | < | + | SELECT * |
- | <li>Get all fields of an object of type Player where the isocode of the country of the object equals to " | + | FROM { Player AS p |
- | </ul> | + | JOIN Country AS c |
- | <pre class=" | + | ON {c:pk} = {p: |
- | | + | AND {c: |
- | | + | } |
- | | + | </code> |
- | | + | |
- | | + | <code sql> |
- | < | + | SELECT {v.code} voucherCode, |
- | <li>Get all the applied voucher and their orders | + | |
- | </ul> | + | |
- | <pre>SELECT {v.code} voucherCode, | + | |
FROM {VoucherInvalidation as vi | FROM {VoucherInvalidation as vi | ||
JOIN Voucher as v | JOIN Voucher as v | ||
Line 40: | Line 38: | ||
} | } | ||
WHERE {vi.status} = ' | WHERE {vi.status} = ' | ||
- | </pre> | + | </code> |
- | <h2 id=" | + | ===== Subqueries |
- | < | + | |
- | <li>Get the pk of the warehouses which have at least one plzRange.</li> | + | <code sql> |
- | </ul> | + | SELECT {ware.pk} |
- | <pre class=" | + | FROM {Warehouse AS ware} |
- | <span class=" | + | WHERE EXISTS ({{ |
- | | + | SELECT 1 |
- | | + | FROM {PLZRange AS plz} |
- | | + | WHERE {ware:pk} = {plz: |
- | | + | }}) |
- | | + | </code> |
- | <h2>Group by</h2> | + | |
- | < | + | ===== Group by ===== |
- | <li>Get the pk of the oldest CMSMedia which is duplicated:</li> | + | |
- | </ul> | + | <code sql> |
- | <pre>SELECT {code}, {catalogversion}, | + | SELECT {code}, {catalogversion}, |
- | <h2>Union</h2> | + | FROM {CMSMedia} |
- | < | + | GROUP BY {code}, {catalogversion} |
- | <li>Get the list of results adding a text. This is an useless example</li> | + | HAVING count(1) |
- | </ul> | + | </code> |
- | <pre>SELECT x.description, | + | |
- | <p>A select of the result of the union is mandatory.</p> | + | ===== Union ===== |
- | <h1>Localized Attributes</h1> | + | |
- | < | + | <code sql> |
- | <li>Using the square brackets you select value of a localized attribute for the given language</li> | + | SELECT x.description, |
- | </ul> | + | FROM ({{ |
- | <pre class=" | + | |
- | from {RendererTemplate as t}</pre> | + | FROM {ScriptExecutionResult as r} |
- | <h1>Usage of the flexibleSearchService</h1> | + | WHERE {r: |
- | <ul> | + | }} UNION ALL {{ |
- | <li>It looks for the deployment environments of the Areco Deployment Manager Extension</li> | + | |
- | </ul> | + | FROM {ScriptExecutionResult as rr} |
- | <pre class=" | + | WHERE {rr: |
- | </ | + | }}) x |
+ | </code> | ||
+ | |||
+ | A select of the result of the union is mandatory. | ||
+ | |||
+ | ====== | ||
+ | | ||
+ | <code sql> | ||
+ | select {t:code}, {t: | ||
+ | from {RendererTemplate as t} | ||
+ | </code> | ||
+ | |||
+ | ====== | ||
+ | |||
+ | | ||
+ | <code java> | ||
+ | @Repository | ||
+ | public class FlexibleSearchDeploymentEnvironmentDAO implements DeploymentEnvironmentDAO { | ||
+ | |||
+ | (...) | ||
+ | | ||
+ | /** | ||
+ | * {@inheritDoc } | ||
+ | */ | ||
+ | | ||
+ | | ||
+ | (...) | ||
+ | |||
+ | final Set<String> normalizedEnvironmentNames = new HashSet<String>(); | ||
+ | for (final String givenEnvironmentName : environmentNames) { | ||
+ | | ||
+ | } | ||
+ | |||
+ | final StringBuilder queryBuilder = new StringBuilder(); | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | final FlexibleSearchQuery query = new FlexibleSearchQuery(queryBuilder.toString()); | ||
+ | | ||
+ | final SearchResult<ScriptExecutionResultModel> searchResult = this.flexibleSearchService.search(query); | ||
+ | if (environmentNames.size() != searchResult.getCount()) { | ||
+ | throw new IllegalStateException(" | ||
+ | + environmentNames); | ||
+ | } | ||
+ | | ||
+ | } | ||
+ | |||
+ | (...) | ||
+ | |||
+ | } | ||
+ | </code> | ||
+ | |||
+ | ====== Conversion of Flexible Search to SQL ====== | ||
- | ===== Conversion of Flexible Search to SQL ===== | ||
* Log into HAC | * Log into HAC | ||
* Go to Console > Flexible Search | * Go to Console > Flexible Search | ||
Line 84: | Line 135: | ||
* The question marks must be replaced with the values of the Hybris' | * The question marks must be replaced with the values of the Hybris' | ||
- | ===== Useful images ===== | + | {{ : |
- | Running a Flexible Search Query on HAC | + | |
- | {{: | + | |
- | How to see the raw SQL on the Flexible Search | + | {{ : |
- | {{: | + | |
- | How to see what parameters where replaced on the query | + | {{ : |
- | {{: | + | |
- | -- Based on SAP Hybris 6.3 | + | -- Based on Hybris 6.6 |