1

I have a tree-like data model stored in a MYSQL database. The tree is traversed by using two fields on each 'node' object:

  • id
  • parent_id

The root id has a parent_id of 'null' and all other nodes in the tree reference a parent id.

If I want to get a list of all node ids in this tree, I have to recursively traverse the tree and collect one or more attributes of each node, using what is essentially a for loop with many queries.

I was wondering if there was a way to do this all more efficiently with on query in SQL

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
E T
  • 311
  • 1
  • 4
  • 12
  • Hope this one help:: http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries – Sashi Kant Feb 02 '13 at 09:24

2 Answers2

2

I wrote a highly-rated post about a solution I call Closure Table: What is the most efficient/elegant way to parse a flat table into a tree?

I also cover that design in my presentation Models for Hierarchical Data with SQL and PHP and in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.

And I've answered questions about hierarchical-data queries many times on Stack Overflow: https://stackoverflow.com/search?q=user%3A20860+%5Bhierarchical-data%5D

Quassnoi has written an epic series of blog articles about querying trees in MySQL: http://explainextended.com/2009/07/22/hierarchial-queries-in-mysql-identifying-trees/

Quassnoi has also answered a number of questions on Stack Overflow about hierarchical-data: https://stackoverflow.com/search?q=user%3A55159+%5Bhierarchical-data%5D

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
1

You are using a difficult model for searching, the Adjacency List Model. If you know the number of potential "levels" of parent-to-child relationships you will have, then yes, you can construct a query to do so. However, if it's not a finite number, then it's going to be pretty difficult.

Consider using the Nested Set Model if possible. Here is a good article on the subject:

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Back to your question, you would have to join on the same table multiple times to get your desired results, something like this:

SELECT *
FROM YourTable t
    LEFT JOIN YourTable t2 ON t2.parentid = t1.id
    LEFT JOIN YourTable t3 ON t3.parentid = t2.id
    LEFT JOIN YourTable t4 ON t4.parentid = t3.id

With an additional JOIN for each potential child-parent relationship.

Hope this helps and good luck.

sgeddes
  • 62,311
  • 6
  • 61
  • 83