Posts

Showing posts with the label dynamic table

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