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"，它应该返回所有属于 "State of Origin" 类别且也属于 "Mens" 类别的产品。但是它只匹配以 "Origin" 开头的产品名称，因为产品级别上的类别不是 "State of 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;

- Phil Rasmussen

---

搜索词“mens origin”是否应该返回该特定子类别的产品？（即，顺序是否重要） - AakashM

@AakashM 不，顺序并不重要，只需要匹配每个关键字。我不需要过于复杂的东西，只需要能够按类别名称进行过滤，除了产品名称/风格之外。我只是无法理解级联CTE以及是否需要第三个CTE才能实现对类别的分层搜索。 - Phil Rasmussen

我猜这一定是一个相当棒的问题，因为没有得到响应 :) - Phil Rasmussen

Answer 1

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

在下面的解决方案过程中，我在CategoryCTE中创建了一个新列，用于保存递归构建的逗号分隔的类别名称列表，然后跟踪所提供的CategoryId的完整树。现在，我有了一个逗号分隔的类别名称列表，然后将其纳入我的第二个CTE中，并执行标准LIKE子句，将产品名称、款式编号和类别名称纳入考虑范围。最后，为了使此搜索更加智能，我使关键字搜索包括所有关键字，以便“男士起源”仅返回与这两个关键字匹配的产品，而不是任何匹配项，并使用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;

- Phil Rasmussen