订购类别名称的最佳方法是与儿童一起使用。

Say I have the following structure in a table:

假设我在一个表中有如下结构:

+-------------+----------------+-----------+
| category_id | name           | parent_id |
+-------------+----------------+-----------+
|           1 | foo            |         0 |
|           2 | bar            |         0 |
|           3 | baz            |         1 |
|           4 | test           |         2 |
|           5 | test1          |         3 |
|           6 | test2          |         5 |
+-------------+----------------+-----------+

Does anyone have any pointers to achieve the following output:

是否有任何指针可以实现以下输出:

foo
bar
foo > baz
bar > test
baz > test1
baz > test 1 > test2

I'm open to changing my schema if it helps, not sure if it is the best method to store the data?

如果有帮助，我愿意改变我的模式，不确定它是否是存储数据的最佳方法?

Thanks

谢谢

3 个解决方案

#1

maybe you want to have a look at nested sets

也许你想看看嵌套的集合。

#2

You could try sorting by parent and then by name:

您可以尝试由父类排序，然后通过名称:

SELECT * FROM category ORDER BY parent_id, name

根据parent_id, name，从类别顺序中选择*。

This will return all categories ordered by name, according to their level (parent).

这将返回按名称排序的所有类别，根据它们的级别(父类)。

#3

The Nested Set Model is a bit more complex to implement than a simple parent-child relationship, but it helps solve problems like this.

嵌套的Set模型比简单的父子关系更复杂一些，但它有助于解决这样的问题。

#1