1

My MySQL table looks like the following:

| id | parentid |
| 1  | 0        |
| 2  | 0        |
| 3  | 1        |
| 4  | 3        |
...

The maximimum depth of the tree is level 10. I'm trying to get all children ids by specifying an id. Currently I'm using a recursive php function that loops over all entries, gets the children and loops over them again. However this method is pretty slow, so I'm trying to find a SQL only method.

My first attempt is:

SELECT lvl0.id, lvl1.id, lvl2.id, lvl3.id ...
FROM tree lvl0
LEFT JOIN tree lvl1 ON lvl1.parentid = lvl0.id
LEFT JOIN tree lvl2 ON lvl2.parentid = lvl1.id
LEFT JOIN tree lvl3 ON lvl3.parentid = lvl2.id
...
WHERE lvl0.id = 3;

This method looks like a promising attempt, however I want to get all children ids distinct in a php array. When I execute this statement, it is pretty fast, but my php code slows it down:

$ids = [];
foreach($result as $row) {
   if(!in_array($row['lvl0.id'], $ids))
      $ids[] = $row['lvl0.id'];
   if(!in_array($row['lvl1.id'], $ids))
      $ids[] = $row['lvl1.id'];
   if(!in_array($row['lvl2.id'], $ids))
      $ids[] = $row['lvl2.id'];
   if(!in_array($row['lvl3.id'], $ids))
      $ids[] = $row['lvl3.id'];
   ...
}

So I try to find a SQL solution, maybe a combination with distinct that allows me to get all distinct children ids in an array.

Patrick
  • 31
  • 3
  • If I understand your problem, it's just that getting the *unique* values from your results query is slow. Have you looked into `array_unique` yet? http://php.net/manual/en/function.array-unique.php – wilkesybear May 02 '16 at 19:20
  • 1
    You should really read: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ – Bogdan Kuštan May 02 '16 at 19:27
  • 1
    There is a thread here with a lot of answers: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Felippe Duarte May 02 '16 at 19:36

0 Answers0