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




Author Image

Ankitkumar Patel

Sr. SAP Hybris consultant, having 6+ years experience in Hybris, Java, J2EE. Extensive hands-on experience in SAP Hybris development, third-party integrations with Hybris, project architecture and design... Read more

Comments

  1. I want get enum values instead of the PK;

    e.g:
    select {m.code}
    from {Account as n}, {PdtFreqEnum AS m} where {m.pk}={n.frequency} provides what I want.

    select {n.frequency} from {Account as n} --> gives you the PKs but I need the codes like the above.

    ReplyDelete
  2. No, you can't get the Enum code without join to Enum type. Because Enum details stores in 'enumerationvalues' table and generally item type(Account) only has reference to it using pk. So your second query always return you the PK.

    ReplyDelete
  3. good one dear. Nice article.

    ReplyDelete

Post a Comment

Popular posts from this blog

How to remove or update all data records in Hybris?

Hybris backoffice customization