Query for updating a node in hierarchial tree structure
some like: https://en.wikipedia.org/wiki/Base36 Suppressing also the slash '/' separator by using fixed length and padding to the encoded id Detailed optimization explanation here: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/ TODO building a function or procedure to split path for retreive ancestors of one item SET @id:= '22'; SELECT Menu_Name, (@id:=Sub_Menu_ID ) as Sub_Menu_ID, Menu_ID FROM ( SELECT Menu_ID, Menu_Name, Sub_Menu_ID FROM menu ORDER BY Sub_Menu_ID DESC ) AS aux_table WHERE Menu_ID = @id ORDER BY Sub_Menu_ID; SELECT id, NAME,'' AS sub Name,'' AS subsub Name,'' AS subsubsub Name FROM Table1 WHERE prent is NULL UNION SELECT b.id,a.name,AS sub Name,'' AS subsub Name,'' AS subsubsub Name FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=WHERE a.prent is NULL AND IS NOT NULL UNION SELECT c.id,a.name,AS sub Name,AS subsub Name,'' AS subsubsub Name FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=LEFT JOIN Table1 AS c ON c.prent=WHERE a.prent is NULL AND IS NOT NULL UNION SELECT d.id,a.name,AS sub Name,AS subsub Name,AS subsubsub Name FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=LEFT JOIN Table1 AS c ON c.prent=LEFT JOIN Table1 AS d ON d.prent=WHERE a.prent is NULL AND IS NOT NULL ORDER BY NAME,sub Name,subsub Name,subsubsub Name Thank you for your interest in this question.Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).Also, by declaring it as an identifying relationship, you can remove nodes from the collection without having to explicitly call Db Context. CREATE PROCEDURE [dbo].[Fill Hierarchy] (@table_name nvarchar(MAX), @hierarchy_name nvarchar(MAX)) AS BEGIN DECLARE @sql nvarchar(MAX), @id_column_name nvarchar(MAX) SET @id_column_name = '[' @table_name '_ID]' SET @table_name = '[' @table_name ']' SET @hierarchy_name = '[' @hierarchy_name ']' SET @sql = '' SET @sql = @sql 'WITH Hierachy(CHILD_ID, PARENT_ID) AS ( ' SET @sql = @sql 'SELECT ' @id_column_name ', [PARENT_ID] FROM ' @table_name ' e ' SET @sql = @sql 'UNION ALL ' SET @sql = @sql 'SELECT e.' @id_column_name ', e.[PARENT_ID] FROM ' @table_name ' e ' SET @sql = @sql 'INNER JOIN Hierachy eh ON e.' @id_column_name ' = eh.[PARENT_ID]) ' SET @sql = @sql 'INSERT INTO ' @hierarchy_name ' ([CHILD_ID], [PARENT_ID]) ( ' SET @sql = @sql 'SELECT [CHILD_ID], [PARENT_ID] FROM Hierachy WHERE [PARENT_ID] IS NOT NULL ' SET @sql = @sql ') ' EXECUTE (@sql) END GO CREATE VIEW [Tree_Hierarchy] AS WITH Hierachy (CHILD_ID, PARENT_ID) AS ( SELECT [My Super Tree_ID], [PARENT_ID] FROM [My Super Tree] AS e UNION ALL SELECT e.[My Super Tree_ID], e.[PARENT_ID] FROM [My Super Tree] AS e INNER JOIN Hierachy AS eh ON e.[My Super Tree_ID] = eh.[PARENT_ID] ) SELECT [CHILD_ID], [PARENT_ID] FROM Hierachy WHERE [PARENT_ID] IS NOT NULL GO CREATE PROCEDURE dbo. Tree t ' SET @sql = @sql 'UNION ALL ' SET @sql = @sql 'SELECT h. Update Hierarchy AS BEGIN DECLARE @sql nvarchar(MAX) SET @sql = '' SET @sql = @sql 'WITH Hierachy(Child Id, Parent Id) AS ( ' SET @sql = @sql 'SELECT t. How would you get tree-structured data from a database with the best performance?For example, say you have a folder-hierarchy in a database.What I finally settled on was the addition of a new gerund entity, along with 2 new entity properties: While writing this up, hazzik posted an answer that is very similar to this approach. As the user opens nodes in the file system tree, we only want to show that user nodes that will, eventually, lead them to the files they can see.
This is know as Modified Preorder Tree Traversal and lets you run a simple query to get all parent values at once. select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (= b.parent),(select @varw:='') as c having list like ',%'; Tree and Tree\Node are PHP classes for handling data that is structured hierarchically using parent ID references.
read more I found it more easily to : 1) create a function that will check if a item is anywhere in the parent hierarchy of another one.
Something like this (I will not write the function, make it with WHILE DO) : Something not mentioned here, although a bit similar to the second alternative of the accepted answer but different and low cost for big hierarchy query and easy (insert update delete) items, would be adding a persistent path column for each item.
It is a "feature" that will be removed in a future My SQL release: operation is not the most ideal way to find a number in a list, certainly not in a list that reaches a size in the same order of magnitude as the number of records returned.
More and more databases implement the SQL:1999 ISO standard syntax for recursive queries (e.g. Some databases have an alternative, non-standard syntax for hierarchical look-ups, such as the clause available on Oracle, DB2, Informix, CUBRID and other databases. When your database engine provides this syntax or you can migrate to one that does, then that is certainly the best option to go for.
Search for query for updating a node in hierarchial tree structure:
So I'm left with a few options 1) Create query, order by depth and then create a custom class in C#, populating it one depth at a time. Note that so far I haven't included your Lineage or Depth properties. Remember also when you will try to save sub-node, then do it just this way: An analogy would be a file structure on disk.