There are a couple pages especially involving nested course categories where on big sites it easy to rack up 100's or even 1000 db calls to the same table when you have a deeply nested category structure.
Most DB's have supported recursive or hierarchical sql for ages and for those that don't the DB method itself could default back to a recursive call internally and produce results in the same way.
All implementations should explicitly forbid circular graphs and just stop when they are detected and still return valid results (with a dev debug).
Sort would only be for siblings at the same leaf level in the tree structure.
Maybe another optional chunk of tables you can join into the primary table if needed.
Internally I'd have an implicit 'parent' and 'child' for the connecting join. Maybe have the option to override those table aliases if we need to.
I'd also include an implicit depth column for free.