How can I get all children from a parent row in the same table?

Let's say I have a table called my_table that looks like this:

id      |  name                    | parent_id
1       |  Row 1                   | NULL
2       |  Row 2                   | NULL
3       |  Row 3                   | 1
4       |  Row 4                   | 1
5       |  Row 5                   | NULL
6       |  Row 6                   | NULL
7       |  Row 7                   | 8
8       |  Row 8                   | NULL
9       |  Row 9                   | 4
10      |  Row 10                  | 4

Basically I want my final array in PHP to look like this:

Array
(
    [0] => Array
        (
            [name] => Row 1
            [children] => Array
               开发者_如何学编程 (
                    [0] => Array
                        (
                            [name] => Row 3
                            [children] => 
                        )

                    [1] => Array
                        (
                            [name] => Row 4
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [name] => Row 9
                                            [children] => 
                                        )

                                    [1] => Array
                                        (
                                            [name] => Row 10
                                            [children] => 
                                        )

                                )

                        )

                )

        )

    [1] => Array
        (
            [name] => Row 2
            [children] => 
        )

    [2] => Array
        (
            [name] => Row 5
            [children] => 
        )

    [3] => Array
        (
            [name] => Row 6
            [children] => 
        )

    [4] => Array
        (
            [name] => Row 8
            [children] => Array
                (
                    [0] => Array
                        (
                            [name] => Row 7
                            [children] => 
                        )

                )

        )

)

So, I want it to get all of the rows where parent_id is null, then find all nested children recursively.

Now here's the part that I'm having trouble with:

How can this be done with 1 call to the database?

I'm sure I could do it with a simple select statement and then have PHP make the array look like this but I'm hoping this can be done with some kind of fancy db joining or something like that.

I'm not aware of any way to get such an array from a single database call. A mysql SQL query returns a table-like dataset which is always column based. Therefore, the answer is you can't.

However, it is possible to make quite smart tree based queries. A very interesting read about this can be found here.

On other systems than mysql, Common table expressions could achieve what you want.

But still no such array out of the box.

No Recursive queries in MySQL, but there's the nested set way of thinking. And the complete reference on it is there: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

AFAIK it is not possible. Unless you write some stored procedure to do the job.

I don't know MySQL, but in SQL Server (and I believe this is ANSI-SQL) this is done with a CTE (Common Table Expression), which takes this form:

WITH MyCTE
(
  -- Non-recursive anchor query
  UNION ALL
  -- Recursive portion that typically JOINs MyCTE to some other table  
)
SELECT * FROM MyCTE;

In MySQL you can create stored procedure which will return you what you need. You can look at the simple example for MSSQL http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm. But it's easy to make for MySQL too

Problem here is that Id's are not stored according to order in which you would traverse your tree. If you fix this problem by building your tree once an then copying all nodes back to the another table assigning Id in accordance to the order in which nodes of the are traversed - next time you will be able to populate your tree by simply scanning forward results of select statement like

SELECT * FROM my_table ORDER BY Id.

This solution won't work if tree changes often because table will have to be regenerated. That can be solved by incrementing Id's initially by large number (say 1000) so that re-numbering will not be necessary every time because you can assign new node Id from the gap. Using fractions for keys also will work nicely if hight of the tree is small.

Simplest way is just to do it in PHP. I had to do something similar in Symfony, but it should be easy to read and adapt. Start off with an array from the table rows.

  public function executeGetTree(sfWebRequest $request)
  {
    $rows = Doctrine_Core::getTable('TreeNode')->findAll();
    $treeNodes = $rows->toArray();

    //set up new array to store children for each parent node
    $nodesContainer = array();
    foreach ($treeNodes as $node){
        $parentId = $node['parent_id'];
        //if node has no parent, it is a root node
        $nodesContainer[isset($parentId)? $parentId : 'root'][] = $node;
    }

    $tree = array();
    //recursively get descendents for each root node
    foreach($nodesContainer['root'] as $rootNode)
    {
      $tree[] = $this->getChildren($nodesContainer, array($rootNode));
    }

    print_r($tree);

    return sfView::NONE;
  }

  private function getChildren(&$container, $parentNode){
      $children = array();
      foreach ($parentNode as $node){
          if(isset($container[$node['id']])){
            $node['children'] = $this->getChildren($container, $container[$node['id']]);
          }
          $children[] = $node;
      } 
      return $children;
  }  

I'm hoping this can be done with some kind of fancy db joining or something like that.

You should consider how impossible that sounds. Consider just one branch in the tree, from 1 -> 4 -> 9 & 10 How would you visualize the relationship in a single row in the MySql result, or any DBMS.

The only sensible option I can think of for hierarchical result is XML, but you don't want to go there given it can be done much better in PHP on the base record.parent structure rather than parsing XML.

Maybe something like this, add levels as appropriate:

SELECT 
    d1.*, 
    d2.`name` AS '2nd Level', 
    d3.`name` AS 'Top Level'
    FROM `my_table` AS d1 
    LEFT JOIN `my_table` AS d2 ON d1.parent_id=d2.id 
    LEFT JOIN `my_table` AS d3 ON d2.parent_id=d3.id