Это мой стол:

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;

У меня есть URL, который выглядит так:products.php?category=5

Из идентификатора категории мне нужно извлечь все категории с одним и тем же родителем на каждом уровне иерархии. Я нашел способ сделать это, но я думаю, что это неэффективно, есть ли лучший способ сделать это?

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