SQL Server CTE hierarchy keyword search

Question

I've run into a tricky issue with recursive searching in an eCommerce shop stored procedure. Basically this single procedure will return all products factoring in basic filters and paging, and using a parent/child category table to perform recursive checks down the hierarchy. This is works beautifully and the CTE's run extremely fast, however the recent addition of a keyword search which needs to search across the Category Name, Product Name, and Style Number has caused dramas.

This seemed quite trivial at first as the 1st CTE already generates a table of all relevant categories in the hierarchy based on the supplied @categoryid and then joins onto the rest of the Product specific tables for all filtering. The Product Name and Style Number search works fine, but I cannot for the life of me get a Category Name search to work because it needs to search the category tree for any matches down the hierarchy tree starting from the top.

---

EDIT: I'm now thinking it may just be a lot easier to add a "tag" table against Products which stores all keyword related tags such as category name, product name and style etc and search directly against the tags.

For example a subset of the Category hierarchy looks like this:

Mens
- Polos
- Jerseys
- Pants

Womens
- Pants
- Shirts
- Polos

Supporters
- State Of Origin
  - Mens
  - Womens
  - Kids
- Bulldogs
   - Jerserys
   - Pants
   - Shirts
   - Caps
- Warratahs

In my sample code below i am passing a search term of "origin mens" which should return all products within the "State of Origin" category that are also within the "Mens" category. The only thing it matches on is Product Names that start with "Origin" and nothing else because the category at the product level is not "State of Origin" as this is the parent. Any help here would be fantastic!

-- Variable Declarations
DECLARE @categoryid int
DECLARE @minprice int
DECLARE @maxprice int
DECLARE @sizefilter int
DECLARE @colourfilter int
DECLARE @searchstring varchar(255)
DECLARE @totalrows int 

-- Variables values for testing
SET @categoryid = 0
SET @minprice = 0
SET @maxprice = 0
SET @sizefilter = 0
SET @colourfilter = 0
SET @searchstring = 'origin mens'

-- Setup paging table
DECLARE @indextable table (rownum int identity(1,1), recordid int);

BEGIN

-- First run CTE recursively over all categories in hierarchy
;WITH categoryCTE AS (

   SELECT cat.id as CategoryId, cat.name as CategoryName
   FROM  dbo.shopcategory AS cat
   WHERE (@categoryid = 0 OR cat.id = @categoryid)
   AND cat.isenabled = 1

   UNION ALL

   SELECT child.id as CategoryId, child.name as CategoryName
   FROM dbo.ShopCategory AS child

        INNER JOIN categoryCTE AS parent 
        ON child.parentid = parent.CategoryId

   WHERE child.isenabled = 1
 ),

 -- Now join CTE onto products tables via linker product_shopcategory
 productsCTE AS (

     SELECT p.id, ppc.shopcategoryid, ppc.listorder as catlistorder
     FROM categoryCTE as cat

         INNER JOIN product_shopcategory ppc ON ppc.shopcategoryid = cat.CategoryId
         INNER JOIN product p ON ppc.productid = p.id
         INNER JOIN productlocality pl ON pl.productid = p.id

         -- ** SEARCH - Join List to Table function of keywords
         INNER JOIN dbo.udf_parseList(@searchString, ' ') s 
         ON (cat.CategoryName + p.Name + p.stylenumber LIKE '%' + s.array_Value + '%')

         LEFT JOIN product_quantity pq ON pq.productid = p.id AND pq.localityid = @localityid
         LEFT JOIN productcolour pc ON pc.productid = p.id
         LEFT JOIN productcolourswatch pcs ON pc.productcolourswatchid = pcs.id
         LEFT JOIN product_productsize pps ON pps.productid = p.id 
         LEFT JOIN productsize ps ON pps.productsizeid = ps.id 

     WHERE p.isenabled = 1

        AND pq.quantity > 1
        AND (pc.isenabled IS NULL OR pc.isenabled = 1)
        AND (@minprice = 0 OR pl.price >= @minprice)
        AND (@maxprice = 0 OR pl.price <= @maxprice)

        -- Colour Group Filters 
        AND (@colourfilter = 0
             OR 
              (pcs.swatchgroupid = @colourfilter AND (pq.productcolourid = pc.id AND pq.quantity > 0))
            )

        -- Size Group Filters
        AND (@sizefilter = 0 
              OR 
              (ps.sizegroupid = @sizefilter AND (pq.productsizeid = pps.productsizeid AND pq.quantity > 0))
            )

 )

-- Create Paging table of results and strip out duplicates with group by
INSERT INTO @indextable (recordid)
    SELECT  DISTINCT id 
    FROM    productsCTE
    GROUP BY id
    ORDER BY id;

Answer 1

Finally solved it! I almost went down the path of creating a full tag table structure so that i could search directly against keyword tags rather than the direct data, however in trying to script a product tags table containing a nesting of the category hierarchy I found the solution which was quite simple.

In the solution procedure below i've created a new column in the CategoryCTE to hold a comma delimited list of category names that is built recursively and this then tracks the full tree for the supplied CategoryId. Now that i have a comma delimited list of Category names, I can then factor this into my 2nd CTE and perform a standard LIKE clause factoring in Product Name, Style Number, and Category Names. Finally in order to make this search a little smarter i made the keyword search inclusive of all keywords so that "mens origin" will only return products matching both of these keywords as oppose to any matches, and this was done using the NOT EXISTS clause.

Hope this helps someone else it performs very fast as well!

-- Variable Declarations
DECLARE @categoryid int
DECLARE @minprice int
DECLARE @maxprice int
DECLARE @sizefilter int
DECLARE @colourfilter int
DECLARE @searchstring varchar(255)
DECLARE @totalrows int 

-- Variables values for testing
SET @categoryid = 0
SET @minprice = 0
SET @maxprice = 0
SET @sizefilter = 0
SET @colourfilter = 0
SET @searchstring = 'origin mens'

-- Setup paging table
DECLARE @indextable table (rownum int identity(1,1), recordid int);

BEGIN

-- First run CTE recursively over all categories in hierarchy inclusive of supplied categoryId
;WITH categoryCTE AS (

   SELECT cat.id as CategoryId, cat.name as CategoryName, 
          CONVERT(varchar(255),cat.name) AS Tags

   FROM   dbo.shopcategory AS cat
   WHERE  (@categoryid = 0 OR cat.id = @categoryid)
   AND    cat.isenabled = 1

   UNION ALL

   SELECT child.id as CategoryId, child.name as CategoryName, CONVERT(varchar(255),
          parent.Tags + CONVERT(varchar(32),',' + child.name)) AS Tags

   FROM dbo.ShopCategory AS child

        INNER JOIN categoryCTE AS parent 
        ON child.parentid = parent.CategoryId

   WHERE child.isenabled = 1
 ),

 -- Now join CTE onto products tables via linker product_shopcategory
 productsCTE AS (

     SELECT p.id, ppc.shopcategoryid, ppc.listorder as catlistorder
     FROM categoryCTE as cat

         INNER JOIN product_shopcategory ppc ON ppc.shopcategoryid = cat.CategoryId
         INNER JOIN product p ON ppc.productid = p.id
         INNER JOIN productlocality pl ON pl.productid = p.id
         LEFT JOIN product_quantity pq ON pq.productid = p.id AND pq.localityid = @localityid
         LEFT JOIN productcolour pc ON pc.productid = p.id
         LEFT JOIN productcolourswatch pcs ON pc.productcolourswatchid = pcs.id
         LEFT JOIN product_productsize pps ON pps.productid = p.id 
         LEFT JOIN productsize ps ON pps.productsizeid = ps.id 

     WHERE p.isenabled = 1

        AND pq.quantity > 1
        AND (pc.isenabled IS NULL OR pc.isenabled = 1)
        AND pl.localityid = @localityid
        AND (@minprice = 0 OR pl.price >= @minprice)
        AND (@maxprice = 0 OR pl.price <= @maxprice)

        -- Keyword Search filter
        AND (@searchstring = '' OR NOT EXISTS
                (
                SELECT  NULL
                FROM    dbo.udf_parseList(@searchString, ' ')
                WHERE   cat.Tags + p.Name + p.stylenumber + pc.stylenumber NOT LIKE '%' + array_Value + '%'
                )
            )

        -- Colour Group Filters 
        AND (@colourfilter = 0
             OR 
              (pcs.swatchgroupid = @colourfilter AND (pq.productcolourid = pc.id AND pq.quantity > 0))
            )

        -- Size Group Filters
        AND (@sizefilter = 0 
              OR 
              (ps.sizegroupid = @sizefilter AND (pq.productsizeid = pps.productsizeid AND pq.quantity > 0))
            )

 )

-- Create Paging table of results and strip out duplicates with group by
INSERT INTO @indextable (recordid)
    SELECT  DISTINCT id 
    FROM    productsCTE
    GROUP BY id
    ORDER BY id;