php - 带有 LIKE 的递归 SELECT 类别面包屑在单个查询中

Question

我正在使用 eBay 类别，我正在寻找最有效的方法来检索匹配的“叶类别”(仅限顶级)列表及其完整的面包屑，当给定一个与部分类别名称匹配的术语时

这是一个sqlfiddle我一直在工作。

假设我只有两个叶子类别(1900 年后和 1900 年前)

这是他们的面包屑

Antiques > Antique Clocks > Bracket Clocks > Post-1900
Antiques > Antique Clocks > Bracket Clocks > Pre-1900

如果使用术语“Bracket”，则结果将包含两行，每个面包屑一行，但如果术语“Post-19”，则只会返回一行。每行应包含两个字段CategoryID 和breadcrumb，CategoryID 必须是“叶类别”。

CREATE TABLE `ebay_categories` (
  `CategoryID` int(11) DEFAULT NULL,
  `CategoryName` varchar(20) DEFAULT NULL,
  `CategoryParentID` int(11) DEFAULT NULL,
  `CategoryLevel` int(11) DEFAULT NULL
);

insert into `ebay_categories` (`CategoryID`, `CategoryName`, `CategoryParentID`, `CategoryLevel`) values('20081','Antiques','20081','1');
insert into `ebay_categories` (`CategoryID`, `CategoryName`, `CategoryParentID`, `CategoryLevel`) values('13851','Antique Clocks','20081','2');
insert into `ebay_categories` (`CategoryID`, `CategoryName`, `CategoryParentID`, `CategoryLevel`) values('100904','Bracket Clocks','13851','3');
insert into `ebay_categories` (`CategoryID`, `CategoryName`, `CategoryParentID`, `CategoryLevel`) values('96762','Post-1900','100904','4');
insert into `ebay_categories` (`CategoryID`, `CategoryName`, `CategoryParentID`, `CategoryLevel`) values('66840','Pre-1900','100904','4');

我正在尝试实现与 here 相同的方法但一直失败得很惨。

SELECT LeafID as CategoryID, GROUP_CONCAT(CategoryName SEPARATOR ' > ') AS breadcrumb FROM (

    (SELECT CategoryID as LeafID AS 

       SELECT * from ebay_categories WHERE CategoryName LIKE '%Antiq%')  AS c

  ) AS b GROUP BY LeafID

) AS a ORDER BY breadcrumb ASC Limit 20

Answer 1

SQL 只能(不“聪明”地使用存储过程等)返回固定数量的列。

(仅供引用 - 你链接到的答案，恕我直言是“聪明的”。它正在用 session 变量等做一些非常明显的事情，如果它们不损害性能就会损害可读性 - 所以我'我将尝试从不同的角度回答您的问题。)

因此，您可以修复(硬编码)面包屑“深度”，并且使用固定数量的 JOIN 语句，一切都变得非常简单。

我假设面包屑深度是 1 到无穷大之间的任何值？即，另一个项目“集合”可以归档在更小的类别深度下？

在这种情况下，您的 GROUP_CONCAT 可能是解决方案的一部分，因为它模拟了 SQL 中的“可变列计数”。 (它返回为 1 列，但内部可以包含灵活数量的分隔值。)

您的问题仍然是 SQL 的性质仍然只能在每个 JOIN 语句中将一个表连接到另一个(单个)表。您的面包屑数据结构已很好地规范化，并假定每个子类别与其父类别都有一个连接。

您可以尝试动态构建 SQL - 但这可能会让您感到吃力。您可能只剩下两个“显而易见的”选项:

1. 存储过程。 (超越基本的 SQL。)
2. 改变你的模式。 (解决数据存储时的问题，而不是检索。)

存储过程可以通过多种方式解决这个问题 - 一个明显的选择是通过程序化构建每个面包屑来迭代，将范围存储在临时表中，然后最终选择整个临时表。

我很乐意就此为您提供指导，但我不会将其作为此答案的一部分(除非有要求)，因为我相当确定性能会非常糟糕，您最终不会想要使用它。

另一个“主要”选项是重构架构。在这种情况下，您所达到的规范化水平使事情变得过于复杂。这在“学术上”很好，而且对磁盘空间也有好处。但它并不能很好地解决您的问题!

去规范化还有另一个主要的权衡。更改模式中的数据时会更加复杂。我建议从编写一个“重建”数据的例程开始(如果你采用这种方法)，因为否则事情会变得不同步，你将永远花时间试图找出问题所在。 (我的经验之谈。)

对于每个匹配的记录(您正在将用户输入与 CategoryName 进行比较)，您希望返回并能够按树中它之前的所有内容进行分组。而且没有做“聪明”的事情。

(几种)非规范化方法之一是为祖先维护一个depth * width 长叶列表。 (正如我所说，它的存储效率不高。您必须评估这是否是生产场景中的问题。)对于您的示例数据，它看起来像这样:

+------------+--------+
| AncestorId | LeafId |
+------------+--------+
| 20081      | 66840  |
| 20081      | 96762  |
| 13851      | 66840  |
| 13851      | 96762  |
| 100904     | 66840  |
| 100904     | 96762  |
| 66840      | 66840  |
| 96762      | 96762  |
+------------+--------+

因此现在你可以做这样的事情:

CREATE TABLE `tree_branches` (
  `AncestorId` int(11) NOT NULL,
  `LeafId` int(11) NOT NULL
);

INSERT INTO `tree_branches` SET `AncestorId`=20081, `LeafId`=66840;
INSERT INTO `tree_branches` SET `AncestorId`=20081, `LeafId`=96762;
INSERT INTO `tree_branches` SET `AncestorId`=13851, `LeafId`=66840;
INSERT INTO `tree_branches` SET `AncestorId`=13851, `LeafId`=96762;
INSERT INTO `tree_branches` SET `AncestorId`=100904, `LeafId`=66840;
INSERT INTO `tree_branches` SET `AncestorId`=100904, `LeafId`=96762;
INSERT INTO `tree_branches` SET `AncestorId`=66840, `LeafId`=66840;
INSERT INTO `tree_branches` SET `AncestorId`=96762, `LeafId`=96762;

SELECT
  GROUP_CONCAT(`breadCrumbCategories`.`CategoryName` SEPARATOR " > ")
FROM `ebay_categories` AS `matchedCategory`
INNER JOIN `tree_branches` AS `matchedCategoryLeaves` ON (`matchedCategoryLeaves`.`AncestorId` = `matchedCategory`.`categoryId`)
INNER JOIN `tree_branches` AS `breadCrumbs` ON (`breadCrumbs`.`LeafId` = `matchedCategoryLeaves`.`LeafId`)
INNER JOIN `ebay_categories` AS `breadCrumbCategories` ON (`breadCrumbCategories`.`CategoryId` = `breadCrumbs`.`ancestorId`)
WHERE
  `matchedCategory`.`CategoryName` LIKE "Post%"
GROUP BY
  `breadCrumbs`.`LeafId`
  ;

您应该为 GROUP_BY 添加某种排序，以确保它不会执行某些隐含的意外操作。您可以(例如)为此目的维护一个关卡 ID。

更新:一旦您理解了我在上面所做的事情，您应该使用 LIKE 'Ant%' 对其进行测试并观察错误输出。添加第二个 GROUP BY 子句和一个 DISTINCT 来解决由用户查询匹配多个面包屑引起的问题，这些面包屑是同一叶的祖先。

SELECT
  DISTINCT GROUP_CONCAT(`breadCrumbCategories`.`CategoryName` SEPARATOR " > ")
FROM `ebay_categories` AS `matchedCategory`
INNER JOIN `tree_branches` AS `matchedCategoryLeaves` ON (`matchedCategoryLeaves`.`AncestorId` = `matchedCategory`.`categoryId`)
INNER JOIN `tree_branches` AS `breadCrumbs` ON (`breadCrumbs`.`LeafId` = `matchedCategoryLeaves`.`LeafId`)
INNER JOIN `ebay_categories` AS `breadCrumbCategories` ON (`breadCrumbCategories`.`CategoryId` = `breadCrumbs`.`ancestorId`)
WHERE
  `matchedCategory`.`CategoryName` LIKE "An%"
GROUP BY
  `breadCrumbs`.`LeafId`,
  `matchedCategory`.`CategoryId`
  ;