|
|
 |
|
|
Today's question comes from Kurt M.:
Kurt, you asked: What I (and probably Mike) would like to know is, is it possible to construct a single SQL statement that will return all of these nodes in their proper depth order with their breadth levels. Probably, but you'd have to be a little tricky about it. Here's what I'm thinking you would need: 1) Multiple recursive joins up to the maximum tree depth you'd like. Of course, there is usually an imposed limit on the number of joins in a query (e.g. SQL6.5 limits you to 16 joins). In reality, there is always a practical limit on the number of joins you can perform. However, if you were really a masochist, you could probably write something that dynamically generates the proper SQL syntax with the correct joins, as long as your tree wasn't too deep.
2) Some way to combine results from the different incarnations of the table
into a set of columns that made sense. Possibly with outer joins and
3) Some way to calculate depth on the fly in the query.... hmm, can't think of a solution to this one off the top of my head. Or you could use the tactics traditional programming languages use: recursion or iteration. A recursive SP (SQL, at least in MS SQLServer does support recursion) could traverse the tree and spit out the results you want. Of course, this would give you multiple resultsets. Or, you could use an iterative approach with a cursor and a stack(temptable). But all that sounds like a lot of trouble. If you don't mind storing a little extra information (hey, disk space is cheap), why not just store a post's depth and root post ID when it's added to the table? That would effectively flatten the information, while still retaining the proper relationships between posts. You could calculate the post's depth with a cute little recursive proc that you call when you insert the post. Then, you can do a straight run through the table, ordering by root post ID, and depth. Sure, it's less elegant, but sometimes less elegant == working solution :) (WARNING, shamless produt plug follows) By the way, I'm on my second Dell Latitude laptop, and it rocks! Sean
Note From Scott Mitchell, webmaster of 4Guys: I also wrote a stored procedure that would return the entire tree structure, using a recursive stored procedure, since we were certain that our depth would, for all practical purposes, be less than 5. So, just wanted to let you know that recursive stored procedures can indeed be used to fully iterate through a complex tree structure.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| |||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||
