Adjacency List Model Select by Path

Question

I'm trying to select a node in my heirarchical system.

I have the "/Path/To/Some/Node" (In exactly that form) and I am trying to figure out how I can get the children of "Node". Naturally "Node" is pseudo-unique, in that it is the only child called "Node" inside of Some, but there could be another "Node" inside of "Path" so you obviously can't just explode and then do a simple Node.

So I need to create a select query which looks down each level...

I could obviously do this by using tonnes of querys, ie.

Select id from Table where name = "Path"
Select id from Table where name = "To" and parent = "$id"
Select id from Table where name = "Some" and parent = "$id"
Select id from Table where name = "Node" and parent = "$id"

This isn't ideal... Can anyone advise?

Answer 1

This article might help:

• http://articles.sitepoint.com/article/hierarchical-data-database

You could extend it to add the "path" column like you mentioned to select original node. Then using a single additional query (as described in the article), you could grab the rest of the nodes in the path.