sql-server - SQL Server CTE 层次结构关键字搜索

Question

我在电子商务商店存储过程中遇到了递归搜索的棘手问题。基本上，这个单一过程将返回基本过滤器和分页中的所有产品，并使用父/子类别表沿层次结构执行递归检查。这工作得很漂亮，CTE 的运行速度非常快，但是最近添加的关键字搜索需要跨类别名称、产品名称和样式编号进行搜索，这引起了争议。

这起初看起来相当微不足道，因为第一个 CTE 已经根据提供的 @categoryid 生成了层次结构中所有相关类别的表，然后连接到其余的产品特定表以进行所有过滤。产品名称和款式编号搜索工作正常，但我一生都无法使类别名称搜索工作，因为它需要从顶部开始在层次结构树中搜索类别树以查找任何匹配项。

<小时/>

编辑:我现在认为针对产品添加“标签”表可能会更容易，该表存储所有与关键字相关的标签，例如类别名称、产品名称和样式等，并直接针对标签进行搜索。

例如，类别层次结构的子集如下所示:

Mens
- Polos
- Jerseys
- Pants

Womens
- Pants
- Shirts
- Polos

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

在下面的示例代码中，我传递了“origin mens”搜索词，该搜索词应返回“原产地州”类别中同时属于“Mens”类别的所有产品。它唯一匹配的是以“Origin”开头的产品名称，没有其他内容，因为产品级别的类别不是“原产地州”，因为这是父级。任何帮助都会很棒!

-- 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

终于解决了!我几乎走上了创建完整标签表结构的道路，以便我可以直接搜索关键字标签而不是直接数据，但是在尝试编写包含类别层次结构嵌套的产品标签表的脚本时，我找到了解决方案很简单。

在下面的解决方案过程中，我在 CategoryCTE 中创建了一个新列，用于保存递归构建的以逗号分隔的类别名称列表，然后跟踪所提供的 CategoryId 的完整树。现在我有了一个以逗号分隔的类别名称列表，然后我可以将其分解到我的第二个 CTE 中，并执行标准的 LIKE 子句分解产品名称、样式编号和类别名称。最后，为了使此搜索更加智能，我进行了包含所有关键字的关键字搜索，以便“mens origin”将仅返回与这两个关键字匹配的产品，而不是任何匹配项，这是使用 NOT EXISTS 子句完成的。

希望这对其他人有帮助，它的性能也非常快!

-- 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;