How can I find all siblings to my node and its anchestors in a hierarchical category tree?

Question

This is my table:

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;

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

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:

(from Managing Hierarchical Data in MySQL)

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.

Bill Karwin · Accepted Answer · 2009-08-22T17:17:04.597

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

Here's a way to do that:

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:

Direct ancestors of CD players
"Uncles" of CD players (siblings of ancestors)
Siblings of CD players
Children of CD players

Let me work on that for a few minutes.

Here's what I've come up with:

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

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.

The depth information appeared to be incorrect until I added DISTINCT: COUNT(DISTINCT c.CategoryId) AS depth — , Aug 22 '09 at 17:12
Thanks, I did not test the query. I've edited to include that fix. — Bill Karwin, Aug 22 '09 at 17:17

score 1 · Answer 2 · answered Aug 22 '09 at 17:37

For the task at hand, the nested set model is next to useless. My suggestion is that you pre-process the table to add a materialized path column, then use that column to answer the question. These two steps together are likely to take less work than any solution that relies on the nested set representation.

Finding the materialized path is a relatively "well-known" problem, but your specific question is not, so here's a query based on paths that answers it.

SELECT
  Name,
  len(MyPath)/4 AS Depth,
  CASE WHEN :PathSelected LIKE MyPath + '%' THEN 1 ELSE 0 END AS Selected
FROM Category
WHERE MyPath = ''
OR (
  MyPath <> ''
  AND MyPath LIKE SUBSTRING(:PathSelected,1,ABS(LEN(MyPath) - 4)) + '____'
);

:PathSelected is (SELECT MyPath FROM Categories WHERE ID = :CategorySelected)), which you can precompute or incorporate into the query. In addition, the ABS protects against a query plan where the SUBSTRING is computed even when MyPath = '' and therefore call SUBSTRING with a negative length parameter.

Here's a full repro in SQL Server syntax for the specific data you used in your question. My query doesn't use the columns lft and rgt, so I didn't include them, but there's no reason you can't have those in your table if they serve other uses.

create table Category(
  Name varchar(25) NOT NULL,
  ID int NOT NULL PRIMARY KEY,
  MyPath varchar(200) NOT NULL
);
GO

insert into Category values
  ('ELECTRONICS',1,''),
  ('TELEVISIONS',2,'/001'),
  ('PORTABLE ELECTRONICS',3,'/002'),
  ('FLASH',4,'/002/001/001'),
  ('MP3 PLAYERS',5,'/002/001'),
  ('2 WAY RADIOS',6,'/002/002'),
  ('CD PLAYERS',7,'/002/003'),
  ('PLASMA',8,'/001/003'),
  ('LCD',9,'/001/002'),
  ('TUBE',10,'/001/001');
GO

DECLARE @Selected int = 7;
DECLARE @MyPath varchar(200) = (
  SELECT MyPath FROM Category
  WHERE ID = @Selected
);

SELECT
  Name,
  len(MyPath)/4 AS Depth,
  CASE WHEN @MyPath LIKE MyPath + '%' THEN 1 ELSE 0 END AS Selected
FROM Category
WHERE MyPath = ''
OR (
  MyPath <> ''
  AND MyPath LIKE SUBSTRING(@MyPath,1,LEN(MyPath) - 4) + '____'
);
GO

DROP TABLE Category;

How can I find all siblings to my node and its anchestors in a hierarchical category tree?

2 Answers2

Linked