meta data for this page
  •  

This is an old revision of the document!

Examples of Flexible Search Queries

<html> <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” /> <h2>Inner Joins</h2> <ul> <li>Get the cms components assigned to a content slot. It doesn't take the catalog version into account.</li> </ul> <pre class=“wiki” style=“display: block;”>SELECT {e2s:target} 

<span class="kw1">FROM</span> <span class="br0">{</span>
  contentslot <span class="kw1">AS</span> s
<span class="kw1">JOIN</span> ElementsForSlot <span class="kw1">AS</span> e2s
  <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>
<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>

<ul> <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> </ul> <pre class=“sql”>SELECT * 

<span class="kw1">FROM</span> <span class="br0">{</span> Player <span class="kw1">AS</span> p
    <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>
      <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>
  <span class="br0">}</span></pre>

<ul> <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
   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' </pre> <h2 id=“toc1”>Subqueries</h2> <ul> <li>Get the pk of the warehouses which have at least one plzRange.</li> </ul> <pre class=“sql”>SELECT {ware.pk} FROM {Warehouse AS ware} 

<span class="kw1">WHERE</span> <span class="kw1">EXISTS</span> <span class="br0">(</span><span class="br0">{</span><span class="br0">{</span>
      <span class="kw1">SELECT</span> <span class="nu0">1</span>
      <span class="kw1">FROM</span> <span class="br0">{</span>PLZRange <span class="kw1">AS</span> plz<span class="br0">}</span>
      <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>

<h2>Group by</h2> <ul> <li>Get the pk of the oldest CMSMedia which is duplicated:</li> </ul> <pre>SELECT {code}, {catalogversion}, min(pk)<br />FROM {CMSMedia}<br />GROUP BY {code}, {catalogversion}<br />HAVING count(1) &gt; 1</pre> <h2>Union</h2> <ul> <li>Get the list of results adding a text. This is an useless example</li> </ul> <pre>SELECT x.description, x.text<br />FROM (canberunnedagain_0_br UNION ALL canberunnedagain_1_br) x</pre> <p>A select of the result of the union is mandatory.</p> <h1>Localized Attributes</h1> <ul> <li>Using the square brackets you select value of a localized attribute for the given language</li> </ul> <pre class=“sql”>select {t:code}, {t:subject[en]}, {t:subject[de]} 

from {RendererTemplate as t}</pre>

<h1>Usage of the flexibleSearchService</h1> <ul> <li>It looks for the deployment environments of the Areco Deployment Manager Extension</li> </ul> <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>

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”

Useful images

Running a Flexible Search Query on HAC HAC - Flexible Search console

How to see the raw SQL on the Flexible Search See the raw SQL on the flexible console

How to see what parameters where replaced on the query Flexible Search query's replaced parameters

– Based on SAP Hybris 6.3

Discussion

Enter your comment. Wiki syntax is allowed: