I am thinking the reply is no, but I'd like it it anybody had any understanding of how you can crawl a tree structure to the depth in SQL (MySQL), however with just one query
More particularly, given a tree structured table (id, data, data, parent_id), and something row within the table, can you really get all descendants (child/grandchild/etc), or for your matter all forefathers (parent/grandparent/etc) not understanding what lengths lower or up it'll go, utilizing a single query?
Or perhaps is with a couple type of recursion require, where I keep querying much deeper until you will find no new results?
Particularly, I am using Ruby and Rails, but I am speculating that's not so relevant.
Thanks ahead of time for just about any advice!!
Yes, you could do, it is a known as an altered Preorder Tree Traversal, as well as referred to here
A functional example (in PHP) is supplied here
Below are some assets:
- http://forums.mysql.com/read.php?10,32818,32818#monosodium glutamate-32818
- Controlling Hierarchical Data in MySQL
Essentially, you will need to perform some kind of cursor inside a saved procedure or query or build an adjacency table. I'd avoid recursion outdoors from the db: for the way deep your tree is, that may get really slow/sketchy.
Celko's technique (nested sets) is fairly good. I additionally used an adjacency table with fields "ancestor" and "descendant" and "distance" (e.g. direct children/parents possess a distance of just one, grandchildren/grandma and grandpa possess a distance of two, etc).
This must be maintained, but is rather simple to do for card inserts: you utilize a transaction, then place the direct link (parent, child, distance=1) in to the table, then Place IGNORE an array of existing parent&children with the addition of distances (I'm able to pull-up the SQL when I've got a chance), which wants a catalog on each one of the 3 fields for performance. Where this method will get ugly is perfect for deletions... you essentially need to mark all of the products which have been affected after which rebuild them. But a benefit of the is it are designed for arbitrary acyclic graphs, whereas the nested set model are only able to do straight hierarchies (e.g. the items except the main has only one parent).
SQL is not a Turing Complete language, meaning you are not likely to have the ability to perform this kind of looping. That you can do some very clever things with SQL and tree structures, however i can't think about a method to describe a row with a certain id "in the hierarchy" for any hierarchy of arbitrary depth.
Your best choice is one thing like what @Serta recommended, which would be to just come with the tree in certain other, more capable language. You are able to really generate a question string inside a general-purpose language utilizing a loop, in which the totally some convoluted number of joins (or sub-queries) which reflects the depth from the hierarchy you're searching for. That might be more effective than looping and multiple queries.