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.
