构建动态层次结构

Question

我有一个查询返回以下格式的行：

ID      NAME                                                            PARENTID
1     Top level.                                                      0
7     Second level1.                                                  1
10   Third Level under Second Level1.                                7
22   Second Level2.                                                  1
23   Third Level1 under Second Level2.                               22
24      Third level2 under Second Level2.                               22

我想要做的是动态构建一个结构。上述数据需要按以下层次结构进行组织：

Top Level.
    Second Level1. 
                Third Level under Second Level1.
    Second Level2.
                Third Level under Second Level2.
                Third Level under Second Level2.

数据可以是任何内容，并且层次结构的深度没有限制。我正在寻找一种编程方式来将这些数据组织成一个结构，无论查询返回什么。我正在 ColdFusion 中编写此内容，因此如果您有 CF 示例那就太好了，但伪代码也可以。感谢您的帮助。

Answer 1

调用该函数并传入0作为parentID和q_query的查询结果。

<cffunction name="generateArray" returntype="Array">
    <cfargument name="parentID" type="Numeric" required="yes" default=0 >
    <cfargument name="q_query" type="query" required="yes">

    <cfquery name="getChildren" dbtype="query">
        SELECT * 
        FROM q_query
        WHERE parentid = <cfqueryparam value="#parentID#" cfsqltype="CF_SQL_INTEGER">
        ORDER BY sequence;
    </cfquery>

    <cfset treeArray = ArrayNew(1)>
    <cfloop query="getChildren">
            <cfquery name="checkForChildren" dbtype="query">
                SELECT * 
                FROM q_query
                WHERE parentid = <cfqueryparam value="#getChildren.id#" cfsqltype="CF_SQL_INTEGER">
            </cfquery>

            <cfset treeStruct = structNew()>
            <cfset treeStruct["id"] = getChildren.id>
            <cfset treeStruct["name"] = getChildren.name>
            <cfset treeStruct["children"] = ArrayNew(1)>
            <cfif checkForChildren.recordcount gt 0>
                <cfinvoke component="cfcs.thisCfc" method="GenerateArray" parentID="#Val(getChildren.ID)#" q_query="#q_query#" returnvariable="children">
                <cfset treeStruct["children"] = children>
            </cfif>
            <cfset temp = ArrayAppend(treeArray, treeStruct)>
    </cfloop>
    <cfreturn treeArray>
</cffunction>