Geek Answers Handbook

Complex strings of sql tree

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 the identifier "852", which is in the middle of the tree menu, but I want to get all the previous related and next related lines, so I want to get the following results:

help me! > love> doesn’t work> try this> done. :) (this result is also shown after the php loop, but starts the loop from start id 1 with the response id 0.

NOTE: The 2200 identifier was not shown as a result, since it is not part of a group.

+2
source share
3 answers

There are several alternatives to simplify working with hierarchical information in SQL:

  • Generic table expressions (SQL-2003 standard) support recursive SQL queries regarding the data type of the parent identifier you are 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 SQL syntax extension that supports recursive queries.

  • Nested kits (left / right solution mentioned by @phantombrain) is a solution described in detail in Joe Selco's book "Trees and Hierarchies in SQL for Smarties", as well as in numerous articles and blogs posting on the Internet.

  • A path enumeration (aka Materialized Path) stores a row in each row in the hierarchy to mark the path of the ancestors of that row. Combine this with LIKE queries to compare the path string with the paths of your predecessors and the paths of descendants.

  • A closure table (called Transitive Closure Relation) uses a second table to store all relationships between descendants and descendants, not just the immediate parent, as in the design you use. Many types of queries become easier after you save all the paths.

  • Hybrid solutions also exist. For example, save the immediate parent id as you do, as well as the root of the tree. Now you can get all the other rows in one hierarchy, get them into the application code and sort the tree using regular data structures.

+3
source

Assuming these are menu items and not something very dynamic, such as a forum, I would recommend changing the scheme to add values ​​to the left and right for each item. The identifiers between the left and right values ​​are all the child elements of the node that you are requesting. Thus, it is easy to get one request to get the left / right values, and the second request to get subitems.

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

+1
source

Recursion is the most elegant way to do this, but I don't think mySql supports it in user-defined functions or stored procedures. I would suggest looping into a temp table or table to get your identifiers, and then join the table and request the results. I don't know mySql very well, so this is not verified, but there is something with this.

 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

0
source

More articles:


All Articles