After I make reference to nested set model I am talking about what's referred to here.

I have to develop a new system for storing "groups" (I can not think about better word for this) inside a user defined hierarchy. Because the nested set model is enhanced for reads rather than creates, I made the decision to make use of that. Regrettably throughout my research and testing of nested sets, I went in to the problem of how do you display the hierarchical tree with sorted nodes. For instance basically possess the hierarchy:

root
    finances
        budgeting
            fy08
    projects
        research
        fabrication
        release
    trash

I would like that to become sorted to ensure that it shows as:

root
    finances
        budgeting
            fy08
    projects
        fabrication
        release
        research
    trash

Observe that the fabrication seems before research.

Anyway, following a lengthy search I saw answer for example "keep tree inside a multi-dimensional array and sort it" and "resort the tree and serialized back to your nested set model" (I am paraphrazing...). In either case, the very first option would be a terrible waste of RAM and CPU, that are both very finite assets... The 2nd solution just appears like lots of painful code.

Regardless, I could learn how to (while using nested set model):

  1. Begin a new tree in SQL
  2. Place a node growing up of some other node in tree
  3. Place a node following a brother or sister node within the tree
  4. Pull the whole tree using the hierarchy structure from SQL
  5. Pull a subtree from the specific node (including root) within the hierarchy without or with a depth limit
  6. Discover the parent associated with a node within the tree

And So I figured #5 and #6 could be employed to perform the sorting I needed, also it doubles to rebuild the tree in sorted order too.

However, since I have looked whatsoever of those things I have learned to complete I observe that #3, #5, and #6 might be used together to do sorted card inserts. Basically did sorted card inserts it continually be sorted. However, basically ever alter the sort criteria or I would like another sort order I am to where you started.

Could this you need to be the limitation from the nested set model? Does its use hinder in query sorting from the output?

I believe this is actually a limitation from the nested set model. You can't easily sort the kid nodes inside their particular parent node, since the ordering from the result set is important to rebuild the tree structure.

It is most likely a great way to help keep the tree sorted when placing, upgrading or removing nodes. This even makes queries extremely fast, which is among the primary goals of the data structure. Should you implement saved methods for those procedures, it's very simple to use.

You may also turn back sort order of the presorted tree. You just need to use ORDER BY node.rgt DESC rather than ORDER BY node.lft ASC.

If you will need to support another sort criteria, you can possible implement it with the addition of another lft and rgt index to every node and this sorted through the other criteria on every place/update/remove.