4

Here is what I have in my MySQL DB table:

id | parent | name
----------------------
1  | null   | Root 1
3  | null   | Root 3
6  | 3      | something 1
7  | 6      | something 2
9  | 1      | something 3

I would like to get the most recent branch out of the table. Right now I just check for highest id and follow the items to the root in code.

In my case I can assume the latest entry is always the tip of a branch and not the middle of a branch or a root.

The problem I have is to get the newest of a specific root. So let's say I want the latest branch of root 3. How would I do that?

Is there a way I can query for this in SQL without having to do recursive loops in PHP until I find what I need?

Changing the way the tree is stored in SQL is not an option at this time. :(

sschueller
  • 533
  • 1
  • 6
  • 18
  • http://stackoverflow.com/questions/12796113/mysql-how-to-find-leaves-in-specific-node This might solve your problem – Naruto Apr 23 '16 at 16:17
  • 1
    Not really, no. Change your data model, or write a sproc, or join the table to itself as often as could be required, or use application level code to handle the recursion, as you suggested. – Strawberry Apr 23 '16 at 16:54

1 Answers1

1

I am suggesting to add a new column with a complete path to that specific node:

id | parent | name          | path 
-----------------------------------
1  | null   | Root 1        | 0|1
3  | null   | Root 3        | 0|3
6  | 3      | something 1   | 0|3|6
7  | 6      | something 2   | 0|3|6|7
9  | 1      | something 3   | 0|1|9

This will not alter your existing data, it will only extend it. This approach is simpler than recursion or stored procedures in computational times. When you need the latest for branch 3 you will do this and get the latest node directly:

SELECT id FROM table where path like "0|3%" 
ORDER BY path DESC LIMIT 1
Elzo Valugi
  • 27,240
  • 15
  • 95
  • 114