有没有办法在不使用递归的情况下从MySQL中获取嵌套数据？

Question

So let us say that I have a menu system with all the navigation items stored in a MySQL table like so:

Table: Menu
-------------------------------------------------------
| id | title      | url                   | parent_id |
-------------------------------------------------------
| 1  | Home       | /home                 | 0         |
| 2  | About      | /about                | 0         |
| 3  | History    | /about/history        | 2         |
| 4  | Location   | /about/location       | 2         |
| 5  | Staff      | /about/staff          | 2         |
| 6  | Articles   | /blog                 | 0         |
| 7  | Archive    | /blog/archive         | 6         |
| 8  | Tags       | /blog/tags            | 6         |
| 9  | Tag Name 1 | /blog/tags/tag-name-1 | 8         |
| 10 | Tag Name 2 | /blog/tags/tag-name-2 | 8         |
-------------------------------------------------------

As you can see this table is quite simple with the only complication being the self referencing column parent_id, which defines how the menu should be nested.

So this would produce the following menu:

- Home
- About
    - History
    - Location
    - Staff
- Articles
    - Archive
    - Tags
        - Tag Name 1
        - Tag Name 2

Is there a way to get this structure from the aforementioned table without making use of a recursive function in PHP (but it could be Python, Java or any other language) that queries the database with each iteration?

Ideally this could be handled with one MySQL query. Perhaps the table structure needs to be changed to accommodate this - if so how?

Answer 1

You could pull all of it out in one single pull, and then work with it recursively in PHP. That way you save some of the query time, but gain a little scripting time.

I would do something like this:

Get all data, ordered by parent id
Put row into $data[$parent_id][]

define function to build menu, takes one param which is id
get $data[$id] and work with that array, building the array.

while looping through the items, check if size of $data[current-item-id] > 0
if so, call above function with 0 as param

This way, you only query the database once, but use a little more of the servers ram.