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.