ColdFusion MX Order Query by Category Tree

- Often I get a query from the database that is in the form of a tree (each record has an ID and a parent ID). - I need a script that will order such a query by the parent ID recursively and return the reordered query - The script should also add a NestLevel column to the new query with a 1 for top level records, a 2 for their children, a 3 for their childrens children and so on. - The script must work with any query/column names - The script must be efficient (not going through the recordset too many times). Sample query: PageID ParentID PageTitle 1 0 Home 2 0 About Us 3 0 Products 4 0 Contact Us 5 2 History 6 3 Product 1 7 3 Product 2 8 5 Recent history Should return: PageID ParentID PageTitle Nest Level 1 0 Home 1 2 0 About Us 1 5 2 History 2 8 5 Recent history 3 3 0 Products 1 6 3 Product 1 2 7 3 Product 2 2 4 0 Contact Us 1 Sample Call: Solution Requirements: Create a method called orderQueryAsCategoryTree() Arguments: - Query - type="query" hint="I am the query to re-order" - IDProperty = type="string" hint="I am the name of the ID property within the query. E.g. UserID" - ParentProperty - type="string" hint="I am the name of the property containing the parent ID within the query. E.g. UserParentID." Returns: - a reordered query (type="query") ## Deliverables 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done. Additional Requirements: - No use of UDFs, custom tags, other methods and no dependencies on anything other than core CF 7.02 features. If you find a UDF that helps on [login to view URL] or elsewhere, just take the underlying code and include it in the method (with well named variables - some of those tags have meaningless variable names). - Can be written using tag or cfscript syntax - ALL variables must be local scoped to make the method thread safe. To save having to var everything, please just add the following script as the first line after the arguments: Then preface al variables with Local. So, if you have a rowcount, call it Local.RowCount. - Please use camel case naming for all variables (e.g. RowCount - capitalized first letter for each word - no underscores or special characters). ## Platform ColdFusion 7.02 Professional Windows 2003 Server (Web edition)