How do you handle them?
Let me give an easy example to explain what I’m talking about:
Table ‘categories’ with the following fields:
As you can see, we have a simple parent-child relationship where for the top categories the parent_category_id is empty for every other lower category it contains the id of the parent category.
Now in theory the resulting tree/nested list can have an infinite depth which makes things harder compared to having a limited depth.
While working on this I came up with 2 possible solutions:
1. Select all top level categories (where parent_category_id is null) and then for each of them select the child categories and then for each of the child categories select their children and so on, could be done pretty easily using recursion but you might end up with many queries resulting in a slow application.
2. Select all categories at once and then use recursion to bring them into a hierarchical structure, e.g. in a multi-dimensional array.
I ended up using the 2. solution as I wanted to keep database usage to a minimum.
So, how would you guys have solved this? Is there a 3. way to do this? Did I miss something entirely?
Tell me what you think.
P.S.: I gotta talk about this with the client this project is for, but maybe I can release the functions I used as a CI helper.