如何在层次结构类别树中找到节点及其固定节点的所有同级节点? [英] How can I find all siblings to my node and its anchestors in a hierarchical category tree?

问题描述

这是我的桌子:

CREATE TABLE IF NOT EXISTS  `Category` (
`Name` varchar(25) NOT NULL,
`lft` INT UNSIGNED NOT NULL,
`rgt` INT UNSIGNED NOT NULL,
`CategoryId` int UNSIGNED auto_increment NOT NULL,
PRIMARY KEY (`CategoryId`)
) Engine = InnoDb;

我有一个看起来像这样的网址:products.php?category=5

I have a url that looks like this: products.php?category=5

我需要从类别ID中检索层次结构每个级别上具有相同父项的所有类别.我找到了一种方法来执行此操作，但我认为它效率低下，是否有更好的方法来执行此操作?

From the category id I need to retrieve all the categories with the same parent at each level of the hierarchy. I found a way to do this but I think it is inefficient, is there a better way to do this?

CREATE VIEW category_tree AS
SELECT node.name as name,
    node.categoryId as categoryId,
    node.lft as lft,
    node.rgt as rgt,
    (COUNT(parent.categoryId) - 1) AS depth
FROM Category AS node,
Category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.categoryId;

SELECT tree.name, tree.depth, tree.categoryId,
(node.lft BETWEEN tree.lft AND tree.rgt) AS is_selected
FROM category_tree as tree
JOIN category_tree AS node ON node.categoryId = :selectedCategory
JOIN (
    SELECT MAX(tree.lft) as lft
    FROM category_tree as tree
    JOIN category_tree AS node ON node.categoryId = :selectedCategory
    WHERE
    tree.depth = node.depth -1
    AND tree.lft < node.lft
) AS parent_finder
LEFT JOIN category_tree AS parent ON parent.lft = parent_finder.lft
WHERE tree.depth < node.depth
OR (
  tree.depth = node.depth
  AND tree.lft BETWEEN parent.lft AND parent.rgt
)
OR (
  tree.lft BETWEEN node.lft AND node.rgt
  AND tree.depth <= node.depth + 1
)
ORDER BY tree.depth, tree.name

例如，假设我的类别树如下:

For example, let's say my category tree looks like this:

(摘自在MySQL中管理分层数据)

(from Managing Hierarchical Data in MySQL)

假设用户选择了"cd播放器"，我想检索以下信息:

Let's say the user has selected "cd players", I want to retrieve the following information:

name                 depth    is_selected
electronics           0            1
portable electronics  1            1
televisions           1            0
mp3 players           2            0
cd players            2            1
2 way radios          2            0

我想检索所有选定的类别以及与选定类别处于同一级别的所有类别，以及深度信息和选定的类别.

I want to retrieve all selected categories and all categories that are at the same level as the selected categories along with depth information and which categories are selected.

我需要这样做的原因是，产品页面可以包含一个下拉导航菜单，用于每个级别的类别，直至所选类别.

The reason I need to do this is so that the products page can include a drop-down navigation menu for every level of category down to the selected category.

推荐答案

我不确定我是否遵循所有这些原则，但这听起来像是希望所有第5类的直接子代.

I'm not sure I follow all that, but it sounds like you want all immediate children of category 5.

这是一种实现方法:

SELECT child.*
FROM Category parent
  JOIN Category child 
    ON (child.lft BETWEEN parent.lft AND parent.rgt)
  LEFT JOIN Category intermediate 
    ON (intermediate.lft > parent.lft AND intermediate.rgt < parent.rgt
      AND child.lft > intermediate.lft AND child.rgt < intermediate.rgt)
WHERE intermediate.CategoryId IS NULL
  AND parent.CategoryId = ?;

---

编辑:好的，我现在知道上面的解决方案只是您想要的一部分.您想要:

---

edit: Okay, I understand now that the solution above is only a part of what you want. You want:

• CD播放器的直接祖先
• CD播放器(祖先的兄弟姐妹)的叔叔"
• CD播放器的兄弟姐妹
• CD播放器的孩子

让我处理几分钟.

这是我想出的:

SELECT descendant.*,
  (current.lft BETWEEN descendant.lft AND descendant.rgt) AS is_selected,
  COUNT(DISTINCT c.CategoryId) AS depth
FROM Category current
JOIN Category selected 
  ON (current.lft BETWEEN selected.lft AND selected.rgt)
JOIN Category descendant 
  ON (descendant.lft BETWEEN selected.lft AND selected.rgt)
LEFT JOIN Category intermediate 
  ON (intermediate.lft > selected.lft AND intermediate.rgt < selected.rgt
    AND descendant.lft > intermediate.lft AND descendant.lft < intermediate.rgt)
JOIN Category c
  ON (descendant.lft BETWEEN c.lft AND c.rgt)
WHERE intermediate.CategoryId IS NULL
  AND current.CategoryId = ?
GROUP BY descendant.CategoryId
ORDER BY depth, descendant.name;

• current是CD播放器
• selected是CD播放器(电子产品，便携式电子产品，CD播放器)的始祖.
• descendant是每个selected祖先的任何子代或孙子等.
• intermediate是每个selected祖先的后代，也是descendant的父代-不得有这些祖先，因此IS NULL是限制性的.
• c是从descendant回到顶部的祖先链，用于确定深度.
• current is CD players
• selected is ancestors of CD players (electronics, portable electronics, CD players)
• descendant is any child or grandchild etc. of each selected ancestor
• intermediate is a descendant of each selected ancestor that is also a parent of descendant -- there must be none of these, hence the IS NULL restriction.
• c is the chain of ancestors from descendant back up to the top, for purposes of determining the depth.

我刚刚意识到我的解决方案还将返回current节点的所有后代.因此，如果您当前正在查看便携式电子设备"，则查询将返回其子级，但也将返回孙子级"flash"，这可能不是您想要的.

I just realized that my solution would also return all descendants of the current node. So if you were currently viewing "portable electronics," the query would return its children, but it would also return the grandchild "flash" which may not be what you want.

这篇关于如何在层次结构类别树中找到节点及其固定节点的所有同级节点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！