|
Ah trees.
Really you only have a few options here. Since rdbms' aren't designed to use inheritance directly in the model, you'll need to either create a tree datastructure (memory heavy), create looping or recursion with many selections (resource heavy), or figure out an ideal balance. If you know exactly what the maximum depth is, you could query it directly.
What I'd suggest is coming up with a scheme that lets you put more burden on the insertion of data into the db (given your second post you are using a db). The idea behind this would be to mock a binary search tree out of it (or even an mtree as you don't really want to enforce that at the db level [although you can depending on the structure you choose]), where you effectively rebalance the tree after every insertion. So for example, you insert a value and it has an id of 1 (don't use auto increment though!). Then you insert its first child, and you give it a value of 2. This is the "right" side of 1. Now you insert another child, but you don't want to call it three, as logically (from the numerical side), that would be the right side of 2. So you alter the values of the current keys, set the 2 to 3, set the 1 to 2, and set the new value to 1. It takes a bit of work to set this up properly, but the plus side is you can issue entire updates with a single query since it will always be relative to an exiting number. The trick is coming up with the numbers to start with.
Now when you select, you'll be able to pull based completely on the numerical values as if it were a numerical search tree. So when I go to select the children from say, Zeus, I can simply select from the table where the id's are either > or < Zeus' parents (so that would include zeus), depending on if Zeus is considerd > or < his parent's. The trick is how you set up Cronus and Rhea to represent a single entity since you cannot base it solely on either Cronus or Rhea.
Determining the leaf's are as simple as determining if they have children.
Ultimately, I think looping is your best option here. At 50x+ generations, you are starting to get a staggering amount of data. Using recursion or an entire tree are not wise. I'd personally take a mix of the above using a binary search approach and mix it in with OO tree structure so that I end up with a tree datastructure that I can search, is limited to just the branch requested, and can display as I want (even fetching just the leaf nodes on a breadth traversal). No matter how you slice it, hierarchy display in PHP and rdbms' is not a simple task.
|