meta data for this page
  •  

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
Next revisionBoth sides next revision
sap_hybris_commerce:import_export:examples-of-flexible-search-queries [2019/08/12 16:27] – created Antonio Robirosasap_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.
  
-<html> +===== Inner Joins ===== 
-<p>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.</p> + 
-<hr id="system-readmore" /> +  Get the cms components assigned to a content slot. It doesn't take the catalog version into account. 
-<h2>Inner Joins</h2> +<code sql
-<ul> +SELECT {e2s:target} 
-<li>Get the cms components assigned to a content slot. It doesn't take the catalog version into account.</li> +  FROM { 
-</ul+    contentslot AS s 
-<pre class="wiki" style="display: block;"><span class="kw1">SELECT</span> <span class="br0">{</span>e2s<span class="sy0">:</span>target<span class="br0">}</span> +  JOIN ElementsForSlot AS e2s 
-  <span class="kw1">FROM</span> <span class="br0">{</span> +    ON {e2s:SOURCE} = {s:pk} } 
-    contentslot <span class="kw1">AS</span> +  WHERE {s:uid} = 'priceChartEvolutionPage_main_slot' 
-  <span class="kw1">JOIN</span> ElementsForSlot <span class="kw1">AS</span> e2s +</code
-    <span class="kw1">ON</span> <span class="br0">{</span>e2s<span class="sy0">:</span><span class="kw1">SOURCE</span><span class="br0">}</span> <span class="sy0">=</span> <span class="br0">{</span>s<span class="sy0">:</span>pk<span class="br0">} }</span> +  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 
-  <span class="kw1">WHERE</span> <span class="br0">{</span>s<span class="sy0">:</span>uid<span class="br0">}</span> <span class="sy0">=</span> <span class="st0">'priceChartEvolutionPage_main_slot'</span> </pre+<code sql
-<ul> +SELECT * 
-<li>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</li> +  FROM { Player AS p 
-</ul+      JOIN Country AS c 
-<pre class="sql"><span class="kw1">SELECT</span> <span class="sy0">*</span> +        ON {c:pk} = {p:country} 
-  <span class="kw1">FROM</span> <span class="br0">{</span> Player <span class="kw1">AS</span> +        AND {c:isocode}='SYR' 
-      <span class="kw1">JOIN</span> Country <span class="kw1">AS</span> +    } 
-        <span class="kw1">ON</span> <span class="br0">{</span>c<span class="sy0">:</span>pk<span class="br0">}</span> <span class="sy0">=</span> <span class="br0">{</span>p<span class="sy0">:</span>country<span class="br0">}</span> +</code
-        <span class="kw1">AND</span> <span class="br0">{</span>c<span class="sy0">:</span>isocode<span class="br0">}</span><span class="sy0">=</span><span class="st0">'SYR'</span> +  Get all the applied voucher and their orders of 2016. It may not work with relative vouchers 
-    <span class="br0">}</span></pre+<code sql
-<ul> +SELECT {v.code} voucherCode, {o.code} orderCode, {o.date} orderDate, {o.TotalDiscounts} orderTotalDiscounts,{d.value} discountValue
-<li>Get all the applied voucher and their orders of  2016. It may not work with relative vouchers</li> +
-</ul+
-<pre>SELECT {v.code} voucherCode, {o.code} orderCode, {o.date} orderDate, {o.TotalDiscounts} orderTotalDiscounts,{d.value} discountValue+
 FROM {VoucherInvalidation as vi  FROM {VoucherInvalidation as vi 
    JOIN Voucher as v    JOIN Voucher as v
Line 40: Line 38:
  }  }
  WHERE {vi.status} = 'confirmed'  WHERE {vi.status} = 'confirmed'
-</pre+</code
-<h2 id="toc1">Subqueries</h2> +===== Subqueries ===== 
-<ul> +  Get the pk of the warehouses which have at least one plzRange. 
-<li>Get the pk of the warehouses which have at least one plzRange.</li> +<code sql
-</ul+SELECT {ware.pk} 
-<pre class="sql"><span class="kw1">SELECT</span> <span class="br0">{</span>ware<span class="sy0">.</span>pk<span class="br0">}</span> +FROM {Warehouse AS ware} 
-<span class="kw1">FROM</span> <span class="br0">{</span>Warehouse <span class="kw1">AS</span> ware<span class="br0">}</span> +  WHERE EXISTS ({{ 
-  <span class="kw1">WHERE</span> <span class="kw1">EXISTS</span> <span class="br0">(</span><span class="br0">{</span><span class="br0">{</span> +        SELECT 1 
-        <span class="kw1">SELECT</span> <span class="nu0">1</span> +        FROM {PLZRange AS plz} 
-        <span class="kw1">FROM</span> <span class="br0">{</span>PLZRange <span class="kw1">AS</span> plz<span class="br0">}</span> +        WHERE {ware:pk} = {plz:WAREHOUSE} 
-        <span class="kw1">WHERE</span> <span class="br0">{</span>ware<span class="sy0">:</span>pk<span class="br0">}</span> <span class="sy0">=</span> <span class="br0">{</span>plz<span class="sy0">:</span>WAREHOUSE<span class="br0">}</span> +    }}) 
-    <span class="br0">}</span><span class="br0">}</span><span class="br0">)<br /><br /></span></pre> +</code
-<h2>Group by</h2> + 
-<ul> +===== Group by ===== 
-<li>Get the pk of the oldest CMSMedia which is duplicated:</li> +  Get the pk of the oldest CMSMedia which is duplicated: 
-</ul+<code sql
-<pre>SELECT {code}, {catalogversion}, min(pk)<br />FROM {CMSMedia}<br />GROUP BY {code}, {catalogversion}<br />HAVING count(1) &gt; 1</pre+SELECT {code}, {catalogversion}, min(pk) 
-<h2>Union</h2> +FROM {CMSMedia} 
-<ul> +GROUP BY {code}, {catalogversion} 
-<li>Get the list of results adding a text. This is an useless example</li> +HAVING count(1) 1 
-</ul+</code
-<pre>SELECT x.description, x.text<br />FROM ({{<br />    SELECT {r:description[en]} description, 'End result' text<br />    FROM {ScriptExecutionResult as r}<br />    WHERE {r:canBeRunnedAgain} = 0<br />}} UNION ALL {{<br />    SELECT {rr:description[en]}, 'Retry result' text<br />    FROM {ScriptExecutionResult as rr}<br />    WHERE {rr:canBeRunnedAgain} = 1<br />}}) x</pre+ 
-<p>A select of the result of the union is mandatory.</p> +===== Union ===== 
-<h1>Localized Attributes</h1> +  Get the list of results adding a text. This is an useless example 
-<ul> +<code sql
-<li>Using the square brackets you select value of a localized attribute for the given language</li> +SELECT x.description, x.text 
-</ul+FROM ({{ 
-<pre class="sql">select {t:code}, {t:subject[en]}, {t:subject[de]} +    SELECT {r:description[en]} description, 'End result' text 
-  from {RendererTemplate as t}</pre+    FROM {ScriptExecutionResult as r} 
-<h1>Usage of the flexibleSearchService</h1> +    WHERE {r:canBeRunnedAgain} = 0 
-<ul> +}} UNION ALL {{ 
-<li>It looks for the deployment environments of the Areco Deployment Manager Extension</li> +    SELECT {rr:description[en]}, 'Retry result' text 
-</ul+    FROM {ScriptExecutionResult as rr} 
-<pre class="sql">@Repository<br />public class FlexibleSearchDeploymentEnvironmentDAO implements DeploymentEnvironmentDAO { <br /><br />  (...)<br />   <br /> /**<br />   * {@inheritDoc }<br />   */<br />  @Override<br />  public Set&lt;DeploymentEnvironmentModel&gt; loadEnvironments(final Set&lt;String&gt; environmentNames) {<br />    (...)<br /><br />    final Set&lt;String&gt; normalizedEnvironmentNames = new HashSet&lt;String&gt;();<br />    for (final String givenEnvironmentName : environmentNames) {<br />      normalizedEnvironmentNames.add(givenEnvironmentName.trim().toUpperCase(Locale.getDefault()));<br />    }<br /><br /><strong>    final StringBuilder queryBuilder = new StringBuilder();</strong><br /><strong>    queryBuilder.append("SELECT {r.").append(ItemModel.PK).append("}").append(" FROM {")</strong><br /><strong>        .append(DeploymentEnvironmentModel._TYPECODE).append(" as r ").append("} ").append(" WHERE ").append(" UPPER({")</strong><br /><strong>        .append(DeploymentEnvironmentModel.NAME).append("}) ").append(" IN ").append('(').append('?')</strong><br /><strong>        .append(DeploymentEnvironmentModel.NAME).append(')');</strong><br /><br /><strong>    final FlexibleSearchQuery query = new FlexibleSearchQuery(queryBuilder.toString());</strong><br /><strong>    query.addQueryParameter(DeploymentEnvironmentModel.NAME, normalizedEnvironmentNames);</strong><br /><strong>    final SearchResult&lt;ScriptExecutionResultModel&gt; searchResult = this.flexibleSearchService.search(query);</strong><br />    if (environmentNames.size() != searchResult.getCount()) {<br />      throw new IllegalStateException("Some environments don't exist. Please check that these names are valid: "<br />          + environmentNames);<br />    }<br />    return new HashSet(<strong>searchResult.getResult()</strong>);<br />  }<br />  <br />  (...)<br />  <br />}</pre+    WHERE {rr:canBeRunnedAgain} = 1 
-</html>+}}) 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<DeploymentEnvironmentModelloadEnvironments(final Set<StringenvironmentNames) { 
 +    (...) 
 + 
 +    final Set<StringnormalizedEnvironmentNames = 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<ScriptExecutionResultModelsearchResult = 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 ======
  
-===== 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' type and catalog versions PKs. You can see these values on the tab "Execution statistics"   * 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"
  
-===== Useful images ===== +{{ :sap_hybris_commerce:import_export:hacrunflexiblesearchquery.png |HAC - Flexible Search console}}Running a Flexible Search Query on HAC
-Running a Flexible Search Query on HAC +
-{{:sap_hybris_commerce:import_export:hacrunflexiblesearchquery.png|HAC - Flexible Search console}}+
  
-How to see the raw SQL on the Flexible Search +{{ :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:hacrunrawsqlqueries.png|See the raw SQL on the flexible console}}+
  
-How to see what parameters where replaced on the query +{{ :sap_hybris_commerce:import_export:hacflexiblesearchseereplacedparameters.png |Flexible Search query's replaced parameters}}How to see what parameters where replaced on the query
-{{:sap_hybris_commerce:import_export:hacflexiblesearchseereplacedparameters.png|Flexible Search query's replaced parameters}}+
  
--- Based on SAP Hybris 6.3+-- Based on Hybris 6.6