Ask a Jedi: Dumping a Recursive Directory List
A reader (nicely) asked me something before I left for Boston, and I never got around to answering. He had an interesting problem. He wanted to list directories and files, in a recursive fashion, using HTML's unordered list display to handle the directories and their children.
Now I thought this was a simple thing - just use the recurse=true option in <cfdirectory>. However - the more I thought about it - the more difficult it seemed. You can sort the <cfdirectory> result - but not in an way you can simply output with HTML.
My first thought was to switch back to a recursive <cfdirectory>, and while that would work, I assumed I'd lose a lot in terms of speed due to all the file operations. So what I came up with was a mix of recursive CFML and the built-in recursive <cfdirectory> tag:
2<cfdirectory directory="#initialDir#" recurse="yes" name="files" sort="directory asc">
3
4<cfset display(files,initialDir)>
5
6<cffunction name="display" returnType="void" output="true">
7 <cfargument name="files" type="query" required="true">
8 <cfargument name="parent" type="string" required="true">
9 <cfset var justMyKids = "">
10
11 <cfquery name="justMyKids" dbtype="query">
12 select *
13 from arguments.files
14 where directory = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.parent#">
15 </cfquery>
16
17 <cfoutput><ul></cfoutput>
18
19 <cfoutput query="justMyKids">
20 <li>#directory#\#name#</li>
21 <cfif type is "Dir">
22 #display(arguments.files, directory & "\" & name)#
23 </cfif>
24 </cfoutput>
25
26 <cfoutput></ul></cfoutput>
27
28</cffunction>
As you can see, I do the initial <cfdirectory> and have it fetch all the files. The UDF simply handles displaying items from the query. I don't normally do output from UDFs, so to be honest, I feel a bit dirty. I'd probably just wrap it up in a cfsavecontent and return that, but this was written in about 5 minutes. Another problem - note I hard code \ as my file delimiter. I could have made this more dynamic by using a Java call:
In general, the use of "/" will work just fine in any OS, however, since I was doing a string comparison in my query, I'd probably want to use the same separator CF used.
I'm interested to see how you handle db sorting and recursion Ray. My method has always been on the sloppy side. Including the same file over and over again until there are no more parents to be found.
Simply moving the closing li in your function so it's after the recursive call to the function accomplishes this.
I only point this out because it's a handy function if you use a menuing system like udm4 (www.udm4.com), but it won't work if the nesting isn't proper.
Oracle has by far the best method of handling hierarchical data with the "start with...connect by prior" syntax.
Given a table with id, name and parentid columns, you can get a sorted tree result like so:
select name, level
from table
start with parentid is null
connect by prior id = parentid
order siblings by name asc
The level column is a pseudo column that gives the depth of each row in the hierarchy, so the root is level 1, it's children level 2, and so on.
The "order siblings by" sorts the results appropriately within each node (in this case, alphabetically by name).
You can get a similar result in other databases using CTE (Common Table Expressions), but not nearly so easily. Here's an article that compares the two methods:
http://www-128.ibm.com/developerworks/db2/library/...
In my experience, from a strictly performance standpoint, either method is preferrable to doing the sorting in the application, particularly if the dataset is large.
That being said, if your requirements dictate database portability, Rick Osborne's method in the link you cited seems to be a pretty decent approach, although I haven't tested it out myself.
http://msdn2.microsoft.com/en-us/library/ms175972....
This covers using CTEs in MSSQL, and combined with the ibm.com link in my previous comment should give you a solid foundation if you want to try this approach.
Ray... Forgive the title, I couldn't resist :-)
Wiggy
http://www.forta.com/blog/index.cfm?mode=entry&...
Doug in Fairbanks
<p>Choose top level folder:<br />
<select name="mnuFolder" onchange="">
<cfdirectory directory="D:\myplace\" name="Parent_Folder">
<cfloop query="Parent_Folder">
<cfif #Parent_Folder.Type# eq "Dir">
<cfoutput>
<option value="#Parent_Folder.Name#\">#Parent_Folder.Name#</option>
</cfoutput>
</cfif>
</cfloop>
</select><br />
Choose subfolder (if any):<br />
<select name="mnuSubFolder">
<cfdirectory directory="D:\myplace\#Parent_Folder.Name#\" name="Sub_Folder">
<cfloop query="Sub_Folder">
<cfif #Sub_Folder.Type# eq "Dir">
<cfoutput>
<option value="#Sub_Folder.Name#\">#Sub_Folder.Name#</option>
</cfoutput>
</cfif>
</cfloop>
</select><br />
<cfparam default="#GetDirectoryFromPath(GetTemplatePath())#/showcase" name="repertoire"/>
<cfdirectory action="list" directory="#repertoire#" name="allDirectories" recurse="true"/>
<cfoutput query="allDirectories" group="name">
<cfif #allDirectories.type# IS "DIR">
<h3>#allDirectories.name#</h3>
<cfelse>
<pre>#allDirectories.name#</pre>
</cfif>
</cfoutput>
Thanks,
Doug Wilder
http://www.stinkylittlefriend.com/blog/index.cfm/2...