hierarchical-data - w3toppers.com

Achieve hierarchy, Parent/Child Relationship in an effective and easy way

Unfortunately, if you can’t change the data model, and you’re using MySQL, you’re stuck in a situation where you need recursive queries and you’re using a DBMS that doesn’t support recursive queries. Quassnoi wrote an interesting series of blog articles, showing techniques for querying hierarchical data. His solutions are quite clever, but very complex. http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ … Read more

Simplest way to do a recursive self-join?

WITH q AS ( SELECT * FROM mytable WHERE ParentID IS NULL — this condition defines the ultimate ancestors in your chain, change it as appropriate UNION ALL SELECT m.* FROM mytable m JOIN q ON m.parentID = q.PersonID ) SELECT * FROM q By adding the ordering condition, you can preserve the tree order: … Read more

Convert a series of parent-child relationships into a hierarchical tree?

This requires a very basic recursive function to parse the child/parent pairs to a tree structure and another recursive function to print it out. Only one function would suffice but here’s two for clarity (a combined function can be found at the end of this answer). First initialize the array of child/parent pairs: $tree = … Read more

Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)

What is the most efficient/elegant way to parse a flat table into a tree?

Now that MySQL 8.0 supports recursive queries, we can say that all popular SQL databases support recursive queries in standard syntax. WITH RECURSIVE MyTree AS ( SELECT * FROM MyTable WHERE ParentId IS NULL UNION ALL SELECT m.* FROM MyTABLE AS m JOIN MyTree AS t ON m.ParentId = t.Id ) SELECT * FROM MyTree; … Read more

What are the options for storing hierarchical data in a relational database?

My favorite answer is as what the first sentence in this thread suggested. Use an Adjacency List to maintain the hierarchy and use Nested Sets to query the hierarchy. The problem up until now has been that the coversion method from an Adjacecy List to Nested Sets has been frightfully slow because most people use … Read more

How to create a MySQL hierarchical recursive query?

For MySQL 8+: use the recursive with syntax. For MySQL 5.x: use inline variables, path IDs, or self-joins. MySQL 8+ with recursive cte (id, name, parent_id) as ( select id, name, parent_id from products where parent_id = 19 union all select p.id, p.name, p.parent_id from products p inner join cte on p.parent_id = cte.id ) … Read more