Query for updating a node in a hierarchial tree structure

" Here's an example query to return all nodes that are descendants of node 1, join them to the Flat Table that contains other node attributes such as SELECT f.name, GROUP_CONCAT(b.ancestor_id order by b.path_length desc) AS breadcrumbs FROM Flat Table f JOIN Closure Table a ON (= a.descendant_id) JOIN Closure Table b ON (b.descendant_id = a.descendant_id) WHERE a.ancestor_id = 1 GROUP BY a.descendant_id ORDER BY ------------ ------------- | name | breadcrumbs | ------------ ------------- | Node 1 | 1 | | Node 1.1 | 1,2 | | Node 1.1.1 | 1,2,4 | | Node 1.2 | 1,6 | ------------ ------------- , presumably to make sure the ordering matches the hierarchy.

It gives a possibility to fetch whole tree structure at once, have the information about the level of the node, its parent node and order within children of the parent node.Let me show you how this would work in Postgre SQL 9.1.The table is represented as a result set, which can be accessed randomly.Pseudo code or plain English is okay, this is purely a conceptional question.(I was not clear enough on that fact, sorry)Tomalak - no the code is pseudo-code. Regarding OOP: Mark Bessey said in his answer: "You can emulate any other data structure with a hashmap, so that's not a terrible limitation.". It probably breaks the rules since I'm creating my own objects but hey I'm doing this as a diversion from real work :) This also assumes that the result Set/table is completely read into some sort of structure before you start building Nodes, which wouldn't be the best solution if you have hundreds of thousands of rows.

Of course you'd have to break things out into proper selects and iterators... Your solution is correct, but I think there is some room fore improvement even without OOP. I always find it hard to filter the algorithm-specific part from the implementation-specific part when presented with a lot of source code.What minimalistic approach would you use to output that to HTML (or text, for that matter) as a correctly ordered, correctly indented tree?Assume further you only have basic data structures (arrays and hashmaps), no fancy objects with parent/children references, no ORM, no framework, just your two hands.Choose the design based on which you'd like to be simpler: writing or reading.If you use nested sets (sometimes referred to as Modified Pre-order Tree Traversal) you can extract the entire tree structure or any subtree within it in tree order with a single query, at the cost of inserts being more expensive, as you need to manage columns which describe an in-order path through thee tree structure.I included a single property in each Node called desc nodes ; ------------- -------------- ------ ----- --------- ---------------- | Field | Type | Null | Key | Default | Extra | ------------- -------------- ------ ----- --------- ---------------- | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(255) | YES | | NULL | | | left Sibling | int(11) | NO | | 0 | | ------------- -------------- ------ ----- --------- ---------------- 3 rows in set (0.00 sec) mysql desc adjacencies; ------------ --------- ------ ----- --------- ---------------- | Field | Type | Null | Key | Default | Extra | ------------ --------- ------ ----- --------- ---------------- | relation Id | int(11) | NO | PRI | NULL | auto_increment | | parent | int(11) | NO | | NULL | | | child | int(11) | NO | | NULL | | | path Len | int(11) | NO | | NULL | | ------------ --------- ------ ----- --------- ---------------- 4 rows in set (0.00 sec) collection and store them all in an assoc array (/hashtable) where the Id is the key.