In order to interact with the database, hybris provides a flexible search module which makes it easy to write queries on the hybris data type instead of a database table directly.
How to write flexible search queries for collection type as an attribute in the item types? Case-1 Select all the approved (article approval status) products but restricted (restricted sales application) in 'mobile' sales applications. Answer- Before jumping into writing a flexible query let's first see the structure of the product, sales application, restricted sales application, and article approval status. 1. SalesApplication: Is an Enum Type
2. Restricted Sales Application Collection: This is a Collection type of Sales Application Enum Type.
3. Restricted Sales Application attribute: Product item type has an attribute of collection type restrictedSalesApplication described in step 2.
4. Article Approval Status: This is an enum type and used as an attribute in Product Item type directly.
Now let's see how collection attribute values are stored in the database. it's a comma-separated list of PKs. (This is why relation works better where data size is huge as the collection comes with the data truncation drawback.) Run the below query on HAC.
select {p.restrictedSalesApplication} from {Product as p}
As you have understood it now you can easily write flexible search queries using like and contact operators as below. When we Join the two table we will have to use LIKE operator for string matching.
select * from {Product as p JOIN SalesApplication as sa ON {p.restrictedSalesApplication} LIKE CONCAT( '%', CONCAT( {sa.PK} , '%' ) ) JOIN ArticleApprovalStatus as aps on {p.approvalStatus}={aps.pk}} where {sa.code} in ('Web') and {aps.code}='approved'
How to use Union and nested queries in Flexible search?
there are different scenarios where you may have to UNION result of two different conditions. For this, you will have to use 'UNION' or 'UNION ALL' keyword as below. here table name is a must because the union will make a derived table and it should have its own alias.
Example- Select all the employees whose 'employeeNo' starts with HXE or all the customers who are active.
