从数据库中获取多个级别的子项？

Question

My terminology is somewhat lacking, so the title for my question is undoubtedly kind of lame, but I will explain what I mean below.

I have a MySQL table that looks something like the following:

categories:

category_id | parent_id

     0            0
     1            0
     2            1
     3            1
     4            3

Now, what I want to do is output the category structure like this:

category structure:

0
1 -> 2
     3 -> 4

In addition to needing to be able to display the category structure, if a category is selected then I want to find all of the articles in that category and in the subcategories (articles would be another table where each article would have a parent_category_id liking it to the category it's in).

The only way I can think of doing this is:

1. Get all categories with parent_id equal to the id of the category being viewed
2. Loop through all of the results and repeat step one
3. Just keep doing that until all of the results have been checked

Is there a better way to do this?

Answer 1

one way to do it in an efficient way is using nested sets. it is a little bit tricky, and its a bit more complicated to update.

It works like that:

every node has 2 id's, and a level. all child nodes ids ar between the nodes ids. example:

category_id | parent_id | low_id |  high_id 
 0            0          1          2
 1            0          3          10
 2            1          4          5
 3            1          6          9
 4            3          7          8

now you can say "give me all child nodes of category 1":

select *
from categories
where low_id between 3 /* (low_id node1) */ and 10 /* (high_id node 1) */

but if you have to insert an node, you need an algorithm to move the other nodes in right position. also it is good to store the level of the nodes, so you don't have to look for the id/parent_id relationship, you only have to sort by low_id and use the level as indicator.

there is a doctrine2 plugin to handle nested sets if you use php as programming language.

edit: i think this is a good point to start: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/