OCC: Category Hierarchy of Available Products (which are available in Stock)

[Former Member]

Hello,

I have to write REST webservice, to return an array of Categories (Category Hierarchy) that have products in stock. Input Parameter - category code and warehouse code.

Flexible query I have prepared -

select distinct {level5.source} from {Product as prod join CategoryProductRelation as level5 on {level5.target} = {prod.pk} join CategoryCategoryRelation as level4 on {level4.target} = {level5.source} join CategoryCategoryRelation as level3 on {level3.target} = {level4.source} join CategoryCategoryRelation as level2 on {level2.target} = {level3.source} join CategoryCategoryRelation as level1 on {level1.target} = {level2.source} join Category as cat on {cat.pk} = {level1.source} JOIN StockLevelProductRelation AS s2pRel ON {s2pRel:TARGET}={prod:PK} JOIN StockLevel AS SL ON {s2pRel:SOURCE}={SL:PK} JOIN Warehouse AS W ON {SL.warehouse}={W.PK} } where {SL.available}>0 and {cat.code}='test_cat' and {W.code}='test_warehouse'

Queries - 1) Category level is not fix, it can be changed. 2) How I can convert the Collection of Category Models to Category Hierarchy.

Any quick help on this would be appreciated !

[Former Member]

REFERENCES:

https://answers.sap.com/questions/12766014/how-to-hide-categories-with-no-products.html https://answers.sap.com/questions/12751504/flexible-search-on-category.html https://answers.sap.com/questions/12752789/how-can-i-get-the-root-category-of-a-product.html

: How to convert category models collection into Hierarchical Structure?