生成存储在MySQL中的类别的面包屑 [英] Generate breadcrumbs of categories stored in MySQL

问题描述

在MySQL中，我以这种方式存储类别:

In MySQL, I store categories this way:

类别: -category_id - 分类名称 -parent_category_id

categories: - category_id - category_name - parent_category_id

为给定category_id生成跟踪/面包屑的最有效方法是什么?

What would be the most efficient way to generate the trail / breadcrumb for a given category_id?

例如 面包屑(category_id): 常规>子1>子2

For example breadcrumbs(category_id): General > Sub 1 > Sub 2

理论上可能存在无限的水平. 我正在使用php.

There could be in theories unlimited levels. I'm using php.

更新: 我看到了这篇文章( http://mikehillyer.com/articles/managing-hierarchical -data-in-mysql/)中有关嵌套集模型的信息. 它看起来很有趣，但是您之前如何动态管理类别? 在纸面上看起来更容易，就像您提前知道类别一样，而在用户可以动态创建/删除/编辑类别时却不然... 你觉得呢?

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?

推荐答案

我喜欢使用

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.

材料化路径模型

使用物化路径模型的想法是将层次结构中的每个节点与其在树中的位置链接起来.这是通过所有节点祖先的串联列表完成的.该列表通常存储在定界字符串中.注意下面的"Linage"字段. CAT_ID NAME CAT_PARENT Lineage 1 Home . 2 product 1 .1 3 CD’s 2 .1.2 4 LP’s 2 .1.2 5 Artists 1 .1 6 Genre 5 .1. 5 7 R&B 6 .1. 5.6 8 Rock 6 .1. 5.6 9 About Us 1 .1

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 "Linage" field below. CAT_ID NAME CAT_PARENT Lineage 1 Home . 2 product 1 .1 3 CD’s 2 .1.2 4 LP’s 2 .1.2 5 Artists 1 .1 6 Genre 5 .1. 5 7 R&B 6 .1. 5.6 8 Rock 6 .1. 5.6 9 About Us 1 .1

遍历表

Select lpad('-',length(t1.lineage))||t1.name listing
From category t1, category t2
Where t1.lineage like t2.lineage ||'%'
    And t2.name = 'Home';
Order by t1.lineage;

列出

Home
-product
–CD’s
–LP’s
-Artists
–Genre
—R&B
—Rock
-About Us

这篇关于生成存储在MySQL中的类别的面包屑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！