生成存储在 MySQL 中的类别的面包屑
在 MySQL 中,我以这种方式存储类别:
类别: - 类别_id - 类别名称 -parent_category_id
为给定的category_id 生成踪迹/面包屑的最有效方法是什么?
例如 面包屑(category_id): 一般>子1> Sub 2
理论上可以有无限的级别。 我正在使用 php。
更新: 我看到这篇文章(http://mikehillyer.com/articles/managing-hierarchical -data-in-mysql/)关于嵌套集模型。 它看起来很有趣,但是您会如何动态管理类别呢? 在纸面上看起来更容易,就像您提前知道类别一样,但当用户可以即时创建/删除/编辑类别时则不然...... 你怎么认为?
In MySQL, I store categories this way:
categories:
- category_id
- category_name
- parent_category_id
What would be the most efficient way to generate the trail / breadcrumb for a given category_id?
For example
breadcrumbs(category_id):
General > Sub 1 > Sub 2
There could be in theories unlimited levels.
I'm using php.
UPDATE:
I saw this article (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) about the Nested Set Model.
It looks interesting, but how would you ago about dynamically managing categories?
It looks easier on paper, like when you know ahead of times the categories, but not when the user can create/delete/edit categories on the fly ...
What do you think?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
我喜欢使用 物化路径方法,因为它本质上包含您的面包屑路径,并且可以轻松执行诸如选择节点的所有后代之类的操作,而无需使用递归查询。
物化路径模型
物化路径模型的思想是将层次结构中的每个节点与其在树中的位置链接起来。这是通过所有节点祖先的串联列表来完成的。该列表通常存储在分隔字符串中。请注意下面的“血统”字段。
遍历表格
列表
I like to use the Materialized Path method, since it essentially contains your breadcrumb trail, and makes it easy to do things like select all descendants of a node without using recursive queries.
Materialized Path model
The idea with the Materialized path model is to link each node in the hierarchy with its position in the tree. This is done with a concatenated list of all the nodes ancestors. This list is usually stored in a delimited string. Note the “Lineage” field below.
Traversing the table
Listing
从传统的父模型生成它(无论您喜欢什么)并缓存它。即时生成它的成本太高,并且层次结构的更改通常比其他更改的频率要低几个数量级。我不会为嵌套集模型而烦恼,因为层次结构会发生变化,然后你必须去玩弄左边和右边。 (请注意,本文仅包含添加和删除的方法 - 不重新设置父子关系 - 这在父模型中非常简单)。
Generate it (however you like) from a traditional parent model and cache it. It's too expensive to be generating it on the fly and the changes to the hierarchy are usually several orders of magnitude less frequent than other changes ever are. I wouldn't bother with the nested sets model since the hierarchy will be changing and then you have to go fooling around with the lefts and rights. (Note that the article only included recipes for adding and deleting - not re-parenting - which is very simple in the parent model).
嵌套集的优点在于,您只需使用一些简单的 SQL 语句即可轻松地在图中添加/删除节点。它实际上并没有那么昂贵,并且可以很快地编码。
如果您碰巧使用 PHP(或者即使您没有),您可以查看此代码以了解 向嵌套集合模型添加节点 (archive.org 备份)。删除(甚至移动)也同样简单。
The beauty of nested sets is that you can easily add/remove nodes from the graph with just a few simple SQL statements. It's really not all that expensive, and can be coded pretty quickly.
If you happen to be using PHP (or even if you don't), you can look at this code to see a fairly straight-forward implementation of adding nodes to a nested set model (archive.org backup). Removing (or even moving) is similarly straightforward.