Posts

Showing posts with the label flexible search

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

Limit the result rows in flexible search

Sometimes, we want to limit the no. of rows in results while we testing the flexible search query from the HAC. Flexible search query supports most of the SQL clauses. So we can use LIMIT or TOP in your query but it's database specific. it might not work with Oracle. e.g SELECT * FROM {Product} LIMIT 100 or SELECT TOP 100 * FROM {Product} For Oracle SELECT * FROM {Product} WHERE rownum <= 100

Though API final FlexibleSearchQuery query = new FlexibleSearchQuery("SELECT * FROM {Product}"); query.setCount(100);