Search code examples
sap-commerce-cloud

HYBRIS Flexible Query to get all the products from Order Model

My requirement is to get the list of customers who have ordered an old product. Here for the old product, we are using an attribute "endproduct". I am able to get all the customers who have placed orders. BUT I don't know how to create a query to get products from Order Model.

I have run this query :

SELECT distinct {c:uid},{aeo:product} from 
{customer as c JOIN order as o on {c:pk}={o:user}JOIN AbstractOrder as ao on {o:pk}={ao:pk} JOIN AbstractOrderEntry as aeo on {ao:pk}={aeo:pk}}

Because AbstractOrderEntryModel has a product attribute.

Solution

  • Try like

    SELECT
distinct {u:uid},{p:name}
FROM { Order AS o JOIN OrderEntry AS oe ON {o.pk} = {oe.order} JOIN Product AS p ON {p.pk} = {oe.product} and {p.endproduct} = '1' JOIN User AS u ON {o.user} = {u.pk}}

    Change endproduct condition as per your requirement.