dou8mwz5079 2012-09-25 18:03
浏览 53
已采纳

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

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?

  • 写回答

1条回答 默认 最新

  • dongyun3335 2012-09-25 18:46
    关注

    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/

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 delta降尺度计算的一些细节,有偿
  • ¥15 Arduino红外遥控代码有问题
  • ¥15 数值计算离散正交多项式
  • ¥30 数值计算均差系数编程
  • ¥15 redis-full-check比较 两个集群的数据出错
  • ¥15 Matlab编程问题
  • ¥15 训练的多模态特征融合模型准确度很低怎么办
  • ¥15 kylin启动报错log4j类冲突
  • ¥15 超声波模块测距控制点灯,灯的闪烁很不稳定,经过调试发现测的距离偏大
  • ¥15 import arcpy出现importing _arcgisscripting 找不到相关程序