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
I want get enum values instead of the PK;
ReplyDeletee.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.
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.
ReplyDeletegood one dear. Nice article.
ReplyDeleteHow to get Products based on a specific category
ReplyDeleteI got what i was looking for. Thank you so much Ankit :)
ReplyDelete