MySQL 嵌套集合层次结构与外部表

发布于 2024-08-27 09:08:32 字数 972 浏览 10 评论 0原文

我在 MySQL 表中使用嵌套集来描述类别层次结构，并使用另一个表来描述产品。

类别表；

id
name
left
right

产品表；

id
categoryId
name

如何检索包含产品所有父类别的完整路径？即：

RootCategory >子类别1>子类别2> ...>>子类别 n >产品

例如，我想列出 SubCategory1 及其子类别中的所有产品，并且对于每个给定的 Product，我想要该产品的完整树路径- 这可能吗？

据我所知 - 但结构不太正确......

select
 parent.`name` as name,
 parent.`id` as id,
 group_concat(parent.`name` separator '/') as path
from
 categories as node,
 categories as parent,
 (select
  inode.`id` as id,
  inode.`name` as name
 from
  categories as inode,
  categories as iparent
 where
  inode.`lft` between iparent.`lft` and iparent.`rgt`
  and
  iparent.`id`=4 /* The category from which to list products */
 order by
  inode.`lft`) as sub
where
 node.`lft` between parent.`lft` and parent.`rgt`
 and
 node.`id`=sub.`id`
group by
 sub.`id`
order by
 node.`lft`

原文

I'm using a nested set in a MySQL table to describe a hierarchy of categories, and an additional table describing products.

Category table;

id
name
left
right

Products table;

id
categoryId
name

How can I retrieve the full path, containing all parent categories, of a product? I.e.:

RootCategory > SubCategory 1 > SubCategory 2 > ... > SubCategory n > Product

Say for example that I want to list all products from SubCategory1 and it's sub categories, and with each given Product I want the full tree path to that product - is this possible?

This is as far as I've got - but the structure is not quite right...

select
 parent.`name` as name,
 parent.`id` as id,
 group_concat(parent.`name` separator '/') as path
from
 categories as node,
 categories as parent,
 (select
  inode.`id` as id,
  inode.`name` as name
 from
  categories as inode,
  categories as iparent
 where
  inode.`lft` between iparent.`lft` and iparent.`rgt`
  and
  iparent.`id`=4 /* The category from which to list products */
 order by
  inode.`lft`) as sub
where
 node.`lft` between parent.`lft` and parent.`rgt`
 and
 node.`id`=sub.`id`
group by
 sub.`id`
order by
 node.`lft`

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

时光是把杀猪刀 2024-09-03 09:08:32

要获取父节点，您只需要...最后一个（SubCategory n）节点的left/right值。

获取您的产品：SELECT ... FROM 产品 p JOIN 类别 c ON c.id = p.category_id WHERE p.id = ?。
获取父级：SELECT ... FROM Category WHERE leftCol <= {productCategory['left']} AND rightCol >= {productCategory['right']}

这就是您所需要的一切。

回复收藏 0 原文

世界如花海般美丽 2024-09-03 09:08:32

嘿，我想我解决了！：D

select
    sub.`name` as product,
    group_concat(parent.`name` separator ' > ') as name
from
    categories as parent,
    categories as node,
    (select
        p.`name` as name,
        p.`categoryId` as category
    from
        categories as node,
        categories as parent,
        products as p
    where
        parent.`id`=4 /* The category from which to list products */
        and
        node.`lft` between parent.`lft` and parent.`rgt`
        and
        p.`categoryId`=node.`id`) as sub
where
    node.`lft` between parent.`lft` and parent.`rgt`
    and
    node.`id`=sub.`category`
group by
    sub.`category`

Hey, I think I solved it! :D

select
    sub.`name` as product,
    group_concat(parent.`name` separator ' > ') as name
from
    categories as parent,
    categories as node,
    (select
        p.`name` as name,
        p.`categoryId` as category
    from
        categories as node,
        categories as parent,
        products as p
    where
        parent.`id`=4 /* The category from which to list products */
        and
        node.`lft` between parent.`lft` and parent.`rgt`
        and
        p.`categoryId`=node.`id`) as sub
where
    node.`lft` between parent.`lft` and parent.`rgt`
    and
    node.`id`=sub.`category`
group by
    sub.`category`

回复收藏 0 原文

~没有更多了~