Tree structure mysql query

Question

i got this table structure :

    -----------------------------------
     Name        DocID       ParentID
    -----------------------------------
     doc1          1           NULL
     doc2          2            1          
     doc3          3           NULL
     doc4          4            3
     doc5          5            1

The query should output the tree structure with parents and childs nodes, the level can have any value. The output is like that :

    doc1
    |  --doc2
    |  --doc5
    | 
    doc3
       --doc4

Can you help to do that in mysql in a simple or recursive query in mysql ?

possible duplicate of [MySQL - Recursing a tree structure](http://stackoverflow.com/questions/2378678/mysql-recursing-a-tree-structure) — Konstantin V. Salikhov, Jul 18 '14 at 09:12
No the number of levels is unknown , it's an infinite tree structure — Safwen, Jul 18 '14 at 09:14
Not possible with MySQL - it still doesn't have recursive queries. — , Jul 18 '14 at 09:18
I understand , but is there any alternative to do that in MYSQL ? — Safwen, Jul 18 '14 at 09:27
possible duplicate of [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) — RandomSeed, Jul 18 '14 at 09:29

score 0 · Answer 1 · answered Jul 19 '14 at 03:59

Writing a query like that for your data model (known as an Adjacency List) would be relatively complex and inefficient. If you really need to do so, check out http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html#adjacency_list_model.

If you don't mind altering your data model, there are two approaches:

Nested Sets - http://en.wikipedia.org/wiki/Nested_set_model. This is the most common and easy to use. There are plenty of scripts and frameworks that facilitate the use of nested sets without requiring you to manage all of the low level operations. If you have existing data, you can loop through the existing rows and insert them into the new nested set table, and you'll be good to go.
Alternatively, you could store the complete hierarchical path of each document (like breadcrumbs) in a new column. Something like:
```
------
Path
------
doc1
doc1>doc2
doc1>doc5
doc3
doc3>doc4
```

The second method would allow you to do a simple SELECT with an ORDER BY on "Path". You could then look at the number of ">" (or whatever character(s) you use) to determine the document's level in the hierarchy (how much to indent it in the UI).

The second method is not ideal because it requires more maintenance. If you change one parent-child relationship, you end up having to regenerate all of the paths. Nested Sets also involve a fair amount of row updates, but it's done very efficiently.

Tree structure mysql query

1 Answers1