Flexible Search Query with JOIN Hybris Example - Hybris Interview question

Flexible Search is an API provided by Hybris to fetch results from Database . You can either use it on hac console or directly in java .

There are some Flexible Query examples which are widely used in Hybris Project .

1. Flexible Query in Hybris to fetch Products :


SELECT * FROM {Product}


We have to put the Model from which the results are required in {}


2. Flexible Query to fetch Products of a particular Catalog

For this type of query we have to use JOIN to as Catalog and Product are different Models in hybris so the data is stored in 2 different tables.


SELECT * FROM {Product AS p
    JOIN Catalog AS  catalog on {p:catalog} = {catalog:pk}}
WHERE 
    {catalog.id} ='electronicProductCatalog'


This above flexible search query will fetch all products of electronicProductCatalog for both Staged and Online Version.


3. Flexible Query in Hybris to fetch Products of a Catalog with Online version

For this type of query we have to use 2 JOIN to as Catalog, Product and CatalogVersion are different Models and  3 different tables.

SELECT * FROM {Product AS p
    JOIN Catalog AS  catalog on {p:catalog} = {catalog:pk}
    JOIN CatalogVersion As ver on {p:catalogVersion} = {ver:pk}
}
WHERE 
    {catalog.id} ='electronicProductCatalog' AND
    {ver.version} = 'Online'


4. Flexible Query in Hybris to fetch Products of a Catalog with Staged version


SELECT * FROM {Product AS p
    JOIN Catalog AS  catalog on {p:catalog} = {catalog:pk}
    JOIN CatalogVersion As ver on {p:catalogVersion} = {ver:pk}
}
WHERE 
    {catalog.id} ='electronicProductCatalog' AND
    {ver.version} = 'Staged'