Okay, I’ve written PHP classes that extend the Zend Framework DB table, row, and rowset classes. I’ve been developing this anyway because I’m speaking at PHP Tek-X in a couple of weeks about hierarchical data models. I don’t want to post all my code to Stack Overflow because they implicitly get licensed under Creative Commons … Read more
Use: SELECT t1.id, t1.parent_id, t1.name, t2.name AS parent_name, t2.id AS parent_id FROM tbl t1 LEFT JOIN tbl t2 ON t2.id = t1.parent_id START WITH t1.id = 1 CONNECT BY PRIOR t1.id = t1.parent_id
Adapted from here: SELECT T2.id, T2.name FROM ( SELECT @r AS _id, (SELECT @r := parent_id FROM table1 WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := 5, @l := 0) vars, table1 h WHERE @r <> 0) T1 JOIN table1 T2 ON T1._id = T2.id ORDER … Read more
If you’re lucky enough to be using SQLite 3.8.3 or higher then you do have access to recursive and non-recursive CTEs using WITH: Thanks to lunicon for letting us know about this SQLite update. In versions prior to 3.8.3, SQLite didn’t support recursive CTEs (or CTEs at all for that matter) so there was no … Read more
I don’t think there is a built-in function in PHP that does this. I tried the following code, and it seems to work to prepare the nested array the way you describe: $nodes = array(); $tree = array(); foreach ($source as &$node) { $node[“Children”] = array(); $id = $node[“Menu”][“id”]; $parent_id = $node[“Menu”][“parent_id”]; $nodes[$id] =& $node; … Read more
If you want to have universal method you”ll need an additional class: public class TreeItem<T> { public T Item { get; set; } public IEnumerable<TreeItem<T>> Children { get; set; } } Then use it with this helper: internal static class GenericHelpers { /// <summary> /// Generates tree of items from item list /// </summary> /// … Read more
Store IDs of the objects in a hash table mapping to the specific object. Enumerate through all the objects and find their parent if it exists and update its parent pointer accordingly. class MyObject { // The actual object public int ParentID { get; set; } public int ID { get; set; } } class … Read more
Yes, this is possible, it’s a called a Modified Preorder Tree Traversal, as best described here Joe Celko’s Trees and Hierarchies in SQL for Smarties A working example (in PHP) is provided here http://www.sitepoint.com/article/hierarchical-data-database/2/
You can use a Nested Set Model as it yields very efficient queries. Check out Managing Hierarchical Data in MySQL and read the section called Nested Set Model. If you’re using an ORM like Doctrine, it includes nested set capabilities. It can be difficult for some to grasp the nested set concepts of left and … Read more
SELECT @id := ( SELECT senderid FROM mytable WHERE receiverid = @id ) AS person FROM ( SELECT @id := 5 ) vars STRAIGHT_JOIN mytable WHERE @id IS NOT NULL