complex sql tree rows

Question

table structure

id    |    message    |    reply_id
1     |    help me!   |    0
434   |    love to    |    1
852   |    didn't work |    434
0110  |    try this   |    852
2200  |    this wont  |    0
5465  |    done. :)   |    0110

i have a id "852" which is middle of tree menu, but i want to get all the previous related and next related rows, so i want to get following results like this:

help me! > love to > didn't work > try this > done. :) (this result shows like that after php loop, but starts looping from starter id 1 with reply id 0.

NOTE: 2200 id didn't show in the result, because its not part of the group.

Answer 1

Assuming these are menu items and not something very dynamic, such as a forum, I would recommend a schema change to add left and right values for each item. The IDs between the left and right values are all children of the node you are querying. Thus, it's easy to do one query to get the left/right values, and a second query to get the sub items.

See http://www.sitepoint.com/print/hierarchical-data-database/ for more information

Answer 2

There are several alternatives for making hierarchical information easier to work with in SQL:

• Common Table Expressions (per the SQL-2003 standard) support recursive SQL queries against the parent-id type of data you're using. So far, MySQL does not support this feature. PostgreSQL 8.4, Microsoft SQL Server, and IBM DB2 are examples of RDBMS brands that support CTE syntax. Oracle also has a proprietary extension to SQL syntax that supports recursive queries.

• Nested Sets (the left/right solution that @phantombrain mentions) is a solution detailed in Joe Celko's book "Trees and Hierarchies in SQL for Smarties" and also in numerous articles and blog postings on the internet.

• Path Enumeration (aka Materialized Path) stores a string in each row in the hierarchy to note the path of ancestors of that row. Combine this with LIKE queries to compare the path string to its ancestors' paths and descendants' paths.

• Closure Table (aka Transitive Closure Relation) uses a second table to store all ancestor-descendant relationships, not just the immediate parent as in the design you're using. Many types of queries become easier once you have all the paths stored.

• Hybrid solutions also exist. For example, store the immediate parent id as you're doing, but also the root of the tree. Now you can get all other rows in the same hierarchy, fetch them into application code, and sort out the tree with conventional data structures.

Answer 3

Recursion is the most elegant way to do this, but I don't think mySql supports it in custom functions or storedprocedures. I would suggest a loop into a temp table or table variable to get your IDs, then join the table and query the results back. I don't know mySql very well so this is untested, but something to this effect.

CREATE TEMPORARY TABLE tbl (myid int, ViewOrder int); 
Set @ifoundID=IdYourLookingFor;
Set @iStartID=@ifoundID;
Set @iOrder=0;
INSERT INTO tbl(myid,ViewOrder)VALUES(@ifoundID,@iOrder);

BEGIN --get the ones going up
 WHILE (@ifoundID Is Not Null) DO 
  SELECT @ifoundID=reply_id FROM YourTable WHERE id=@ifoundID; --find the next id
  SET @iOrder1=@iOrder-1; --increment the order
  INSERT INTO tbl(myid,ViewOrder)VALUES(@ifoundID,@iOrder);--save the nextid
 END WHILE;
END

Set @ifoundID=@iStartID;
BEGIN --get the ones going down
 WHILE (@ifoundID Is Not Null) DO 
  SELECT @ifoundID=id FROM YourTable WHERE reply_id=@ifoundID; --find the next id
  SET @iOrder1=@iOrder+1; --increment the order
  INSERT INTO tbl(myid,ViewOrder)VALUES(@ifoundID,@iOrder);--save the nextid
 END WHILE;
END

SELECT * FROM tbl INNER JOIN YourTable ON tbl.myid=YourTable.id ORDER BY ViewOrder

Hope that helps