Differences

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

--- 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 [2020/06/29 12:03] (current) – [Outer joins] 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> s
+  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> p
+        AND {c:isocode}='SYR'
-      <span class="kw1">JOIN</span> Country <span class="kw1">AS</span> c
+    }
-        <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
    JOIN Voucher as v
@@ Line 40: / Line 38: @@
  }
  WHERE {vi.status} = 'confirmed'
-</pre>
+</code>
-<h2 id="toc1">Subqueries</h2>
+===== Outer joins =====
-<ul>
+  * Modified point of services or their address
-<li>Get the pk of the warehouses which have at least one plzRange.</li>
+<code sql>
-</ul>
+SELECT {p.pk }
-<pre class="sql"><span class="kw1">SELECT</span> <span class="br0">{</span>ware<span class="sy0">.</span>pk<span class="br0">}</span>
+FROM {PointOfService as p
-<span class="kw1">FROM</span> <span class="br0">{</span>Warehouse <span class="kw1">AS</span> ware<span class="br0">}</span>
+LEFT OUTER JOIN Address as a
-  <span class="kw1">WHERE</span> <span class="kw1">EXISTS</span> <span class="br0">(</span><span class="br0">{</span><span class="br0">{</span>
+  ON {p.address} = {a.pk}}
-        <span class="kw1">SELECT</span> <span class="nu0">1</span>
+WHERE {p.modifiedtime} >= '2020-10-01'
-        <span class="kw1">FROM</span> <span class="br0">{</span>PLZRange <span class="kw1">AS</span> plz<span class="br0">}</span>
+  OR {a.modifiedtime} >= '2020-10-01'
-        <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>
+ORDER BY {p.baseStore}, {p.creationtime} DESC
-    <span class="br0">}</span><span class="br0">}</span><span class="br0">)<br /><br /></span></pre>
+</code>
-<h2>Group by</h2>
-<ul>
+===== Subqueries =====
-<li>Get the pk of the oldest CMSMedia which is duplicated:</li>
+  * Get the pk of the warehouses which have at least one plzRange.
-</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 {ware.pk}
-<h2>Union</h2>
+FROM {Warehouse AS ware}
-<ul>
+  WHERE EXISTS ({{
-<li>Get the list of results adding a text. This is an useless example</li>
+        SELECT 1
-</ul>
+        FROM {PLZRange AS plz}
-<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>
+        WHERE {ware:pk} = {plz:WAREHOUSE}
-<p>A select of the result of the union is mandatory.</p>
+    }})
-<h1>Localized Attributes</h1>
+</code>
-<ul>
-<li>Using the square brackets you select value of a localized attribute for the given language</li>
+===== Group by =====
-</ul>
+  * Get the pk of the oldest CMSMedia which is duplicated:
-<pre class="sql">select {t:code}, {t:subject[en]}, {t:subject[de]}
+<code sql>
-  from {RendererTemplate as t}</pre>
+SELECT {code}, {catalogversion}, min(pk)
-<h1>Usage of the flexibleSearchService</h1>
+FROM {CMSMedia}
-<ul>
+GROUP BY {code}, {catalogversion}
-<li>It looks for the deployment environments of the Areco Deployment Manager Extension</li>
+HAVING count(1) > 1
-</ul>
+</code>
-<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>
-</html>
+===== 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 ======
-===== Conversion of Flexible Search to SQL =====
   * Log into HAC
   * Go to Console > Flexible Search
@@ Line 84: / Line 147: @@
   * 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

Tools

menus and quick search

quick search

site status

location indicator

Page Tools

meta data for this page

Differences