Hybris flexible search query examples

Scenario1: Basic JOIN and IN query syntax

Get the most recent order for each customer using flexible search.

select {o.code} as orderCode,
       {c.name} as name,
       {a.cellphone} as cellphone

from   {order as o 
        join Customer as c on {c.pk} = {o.user}  
        join Address as a on {o.deliveryaddress} = {a.pk}
       } 

       where {o.code} in ({{select max({code}) from {order} group by {user}}})

---

Note: This query is not optimized one. This is just the reference to the temporary table join syntax

select t1.orderCode, t1.name, t1.cellphone 
from 
({{
    select {o.code} as orderCode, {c.name} as name,{a.cellphone} as cellphone 
    from {order as o join Customer as c on {c.pk} = {o.user} join Address as a 
    on {o.deliveryaddress} = {a.pk}}
}}) as t1

Join

({{
  select max({code}) as orderCode, {user} as user from {order} group by {user}
}}) as t2

on t1.orderCode = t2.orderCode

---

Scenario2: Temporary table and join between them

Fetch all registered customers and their last order information

select t1.name, t2.orderCode, t2.cellphone

from 

({{
  select {pk} as userPk, {name} as name from {Customer}
}}) as t1

LEFT JOIN

({{
    select 
       {o.code} as orderCode,
       {o.user} as user,
       {a.cellphone} as cellphone

from   {order as o 
        join Address as a on {o.deliveryaddress} = {a.pk}
       } 

       where {o.code} in ({{select max({code}) from {order} group by {user}}})
}}) as t2

on t2.user = t1.userPk

---

Scenario3: Look for the value in the collection type

Search in Collections to check if the value exists in the collection type attribute.

<collectiontype code="PaymentModeCollection" elementtype="PaymentMode" autocreate="true" generate="false"/>

<itemtype code="DeliveryMode"
          extends="GenericItem"
          jaloclass="de.hybris.platform.jalo.order.delivery.DeliveryMode"
          autocreate="true"
          generate="true">
    <attributes>
        <attribute qualifier="code" type="java.lang.String" autocreate="true" generate="true">
            <persistence type="property"/>
            <modifiers read="true" write="true" search="true" optional="false" unique="true"/>
            <custom-properties>
                <property name="hmcIndexField">
                    <value>"thefield"</value>
                </property>
            </custom-properties>
        </attribute>
        <attribute autocreate="true" qualifier="supportedPaymentModes" type="PaymentModeCollection"
                   generate="true">
            <persistence type="jalo"/>
            <modifiers read="true" write="true" search="false" optional="true"/>
        </attribute>
    </attributes>
</itemtype>

Here let's say you want to get the name of the all PaymentMode which is there as supported payment mode of DeliveryMode table. Your query would be like.

SELECT {pm.name}, {pm.code}
FROM
{
   DeliveryMode AS dm JOIN PaymentMode AS pm
   ON {dm.supportedPaymentModeInternal} LIKE CONCAT( '%', CONCAT( {pm.PK} , '%' ) )
} 

---

Scenario4:  CASE WHEN example

Here we want to sort the product review by numbers of like it got

SELECT {c.pk}
FROM { CustomerReview c LEFT JOIN
     LikeReview l
     ON {c.pk} = {l.customerReview} }
WHERE {c.product} = ?product AND
      {c.LANGUAGE} = ?language
GROUP BY {c.pk}
ORDER BY SUM(CASE WHEN {l.ISLIKE} = "true" THEN 1 ELSE 0 END) DESC

---

Scenario5: HAVING CLAUSE example

Get the list of user who has more than one cart

select {u.pk}, {b.pk}, count(distinct({c.pk})) 
from {
   user as u 
   join cart as c on {c.user} = {u.pk} 
   join basestore as b on {b.pk} = {c.store}
}

WHERE {c.saveTime} IS NULL AND {c.quoteReference} IS NULL
GROUP BY {u.pk}, {b.pk}
having count(distinct({c.pk})) > 1

---