Commerce Server API is not exactly known for being lightning-fast, so sometimes you want to skip it altogether and just use plain old SQL to get the data.
This time I needed to find all categories (at a particular level) that a product belongs to. This was solved by the script below, which is using a recursive CTE to walk upwards through the tree.
Just set the @productId and @hierarchyLevel below and voilá!
DECLARE @hierarchyLevel INT DECLARE @productId VARCHAR(10) SET @hierarchyLevel = 3 SET @productId = '25853' ; WITH Cats (oid, child_oid, hierarchy_level) AS ( SELECT h.oid, h.child_oid, 1 AS hierarchy_level FROM dbo.ConsumerShop_CatalogHierarchy h JOIN dbo.ConsumerShop_CatalogProducts p ON h.child_oid = p.oid WHERE p.ProductID = @productId AND h.CatalogName = 'ConsumerStaging' UNION ALL SELECT h.oid, h.child_oid, c.hierarchy_level + 1 AS hierarchy_level FROM dbo.ConsumerShop_CatalogHierarchy h JOIN Cats c ON c.oid = h.child_oid WHERE h.CatalogName = 'ConsumerStaging' ) SELECT CategoryName FROM Cats JOIN dbo.ConsumerShop_CatalogProducts p ON Cats.oid = p.oid WHERE hierarchy_level = @hierarchyLevel AND BaseCatalogName = 'ConsumerStaging'
In my next post I will explain how to accomplish the opposite, i.e. find all subcategories of a category.