Creating nested tree structure from ParentID's in a db

Question

This seems like a pretty common task I want to do, but I can't wrap my head around the cfloops and cfqueries.

I have a database full of photo galleries. They all have an ID and a ParentID (except the root galleries - their ParentID is blank), and they can go multiple levels deep. Here's an example of the gallery structure:

• 1
  • 1-1
  • 1-2
• 2
  • 2-1
    • 2-1-1
    • 2-1-2
  • 2-2

I want to automatically output this structure above into nested ul's and li's (just like above). How is that done? I can't wrap my head around the placement of the ul's and li's to display the hierarchy levels correctly.

Answer 1

I guess the simpliest solution would be to use Query of Query...

If the dataset is huge, try <cftree> and populate the leaves on demand.

Answer 2

I would use a query of queries to do this recursively.

Note: the code below is NOT tested, so please treat is as example psuedocode.

<cfquery query="qReadAllData">
  select * from your_table
</cfquery>

<!--- Read all roots (no parent ID) --->
<cfquery query="qReadRoots" dbtype="query">
  select nodeID from qReadAllData
  where parentID is null
</cfquery>

<ul>
  <cfloop query="qReadRoots">

    <cfset processNode(qReadRoots.nodeID) />

  </cfloop>
</ul>


<cffunction name="processNode" output="true">
  <cfargument name="nodeID" type="any" />

  <!--- Check for any nodes that have *this* node as a parent --->
  <cfquery query="LOCAL.qFindChildren" dbtype="query">
    select nodeID from qReadAllData
    where parentID = #ARGUMENTS.nodeID#
  </cfquery>

  <cfif LOCAL.qFindChildren.recordcount>

    <!--- We have another list! --->
    <li>
      <ul>
        <!--- We have children, so process these first --->
        <cfloop query="LOCAL.qFindChildren">

          <!--- Recursively call function --->
          <cfset processNode(LOCAL.qFindChildren.nodeID) />

        </cfloop>

      </ul>
    <li>


  <cfelse>

     <!--- We have no more children, so we just output the value --->
     <li>#nodeID#<li>

  </cfif>

</cffunction>

It's late. I'm tired. I hope this is right :)

Answer 3

This is a mix of both SQL and Coldfusion. It's probably not the best to format the label in SQL Server, but it does give the desired format.

SQL:

CREATE TABLE testTable(id int, parentID int)
INSERT INTO testTable(id, parentID) VALUES
    (1, NULL)
    , (2, 1)
    , (3, 1)
    , (4, NULL)
    , (5, 4)
    , (6, 5)
    , (7, 5)
    , (8, 4)

Coldfusion:

<cfscript>
    qs = new query();
    qs.setDatasource("datasource");
    qs.setSQL("
        ;WITH cte AS
        (
            SELECT t.ID, t.parentID, 1 AS level, 
                CAST(DENSE_RANK() OVER (PARTITION BY t.parentID ORDER BY t.ID) AS varchar(max)) AS label
            FROM testTable t
            WHERE parentID IS NULL

            UNION ALL 

            SELECT t.ID, t.parentID, cte.level + 1 AS level, 
                CAST(cte.label AS varchar(max)) + ' - ' + CAST(DENSE_RANK() OVER (PARTITION BY t.parentID ORDER BY t.ID) AS varchar(max)) AS label
            FROM testTable t
                INNER JOIN cte ON cte.ID = t.parentID
        )
        SELECT *,
            DENSE_RANK() OVER (PARTITION BY parentID ORDER BY ID) AS [order]
        FROM cte
        ORDER BY label
    ");
    qMenu = qs.execute().getResult();

    oldLevel = 0;

    for (i=1;i<=qMenu.recordCount;i++){        
        if (qMenu.level[i] > oldLevel) {
            WriteOutput("<ul>");
        }

        while (qMenu.level[i] < oldLevel) {
            WriteOutput("</ul>");
            oldLevel--;
        }

        WriteOutput("<li>" & qMenu.label[i] & "</li>");

        oldLevel = qMenu.level[i];

    }

    do {
        WriteOutput("</ul>");
        oldLevel--;
    } while (oldLevel > 0);
</cfscript>

Answer 4

Here's what you need :

T-SQL Stored Procedure for Looping a hierarchy with recursion

Let me know if you need help implementing it with your table

Update: See new answer with query to update with depth for easy select / output.

Answer 5

Here you go, if you give the actual data structure I'll alter to match your actual fields :

You can add a depth and lineage(fullPath) column to your table, then you can just do one simple select to get the heirarchy and output it accordingly. One query, done.

WHILE EXISTS (SELECT * FROM Tree WHERE levelDeep Is Null) 
UPDATE T SET T.levelDeep = P.levelDeep + 1, 
T.fullPath= P.fullPath+ Ltrim(Str(T.ParentNode,6,0)) + '/' 
FROM Tree AS T 
INNER JOIN Tree AS P ON (T.ParentNode=P.Node) 
WHERE P.levelDeep >=0 
AND P.fullPathIs Not Null 
AND T.levelDeep Is Null