StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server CTE hierarchy keyword search
text
Body
copied!<p>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. </p> <p>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.</p> <hr> <h2>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.</h2> <p>For example a subset of the Category hierarchy looks like this:</p> <pre><code>Mens - Polos - Jerseys - Pants Womens - Pants - Shirts - Polos Supporters - State Of Origin - Mens - Womens - Kids - Bulldogs - Jerserys - Pants - Shirts - Caps - Warratahs </code></pre> <p>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!</p> <pre><code>-- 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; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload