Sort directory listing with coldfusion

I have the following code and need to sort the resulting list in Alpha order.  I'm sorting a folder and using the file contents to dynamicaly create href links to those file.

I believe I have everything correct but it is sorting like this:
file1, file10, file2, file21, file3

I want it to be: file1, file2, file21, file3

What is missing or incorrect with the code?

<!--- Sets directory path --->
<cfset documentsDir = ExpandPath( "./meetingDocs/TechEnvMeeting11-20-08/" ) />
<cfdirectory directory="#documentsDir#" name="dirQuery" listinfo="all" action="list" type="file">
<!--- Get an array of directory names. --->
<cfset dirsArray=arraynew(1)>
<cfset i=1>
<cfloop query="dirQuery">
<cfif dirQuery.type IS "dir">
    <cfset dirsArray[i]>
    <cfset i = i + 1>
<cfquery dbtype="query" name="finalSort">
select * from dirQuery order by asc
<cfoutput query="finalSort">
        <li><a href="./meetingDocs/TechEnvMeeting11-20-08/#name#">#name#</a></li>

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

erikTsomikSystem Architect, CF programmer Commented:
try this
select name from dirQuery order by name
PhotoMan2000Author Commented:
No change in the order. Also tried:
select from dirQuery order by

this is expected order by behaviour

order by looks at chars like this

   1, 10,
   2, 21,

I'll see if i can knock together something that works for you
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PhotoMan2000Author Commented:
Thanks. BTW this is running on a Windows server.  The strange thing is that when I view the file in the folder, it sorts correctly - Windows must sort in a differently?  I know that 1,11,2,21,3 is the correct sort order but humans are dumb!
this is an issue with QoQ's (and is a lot more complicated than I first thought!)

PhotoMan2000Author Commented:
OK. is there any sort of solution or do I have to suffer with it? :)
I figured the best way to deal with this is to get the numeric values using regex and then sort based on those integer values...great tidea but it doesn't work with QoQ

QoQ uses typeless sorts so you always get a sort like 1, 12,2,21,3 even on integer types (which is irritating to say the least)

I found a custom tag that does what you need though once you've used my regex bit download it free here

<cfset dirQuery= querynew("")>
<cfset queryaddcolumn  (dirQuery, "Col1", "CF_SQL_varchar", ListToArray("alongfile1,file12,file2,file23,file3"))>
<!--- above would be replaced by cfdirectory --->
<cfquery dbtype = "query" name="test">
select dirQuery.col1 
from dirQuery 
<!--- create an array --->
	<cfset ord = arraynew(1)>
<cfloop query="test">
<cfset  arrayAppend(ord,
<!--- find the numbers in each file so we can sort on that --->
<!--- append array to query --->
<cfset queryaddcolumn  (dirQuery, "Col2", "CF_SQL_integer", ord)>
<cf_querySort query="dirQuery" SortColumn="col2" sortorder="asc" sorttype="numeric"
<!--- sort on the column of numbers we added --->
<cfdump var="#dirQuery#">
<cfdump var="#final#">

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
You did not specify if files in your folder have extension (it is returned as part of #name# variable from <cfdirectory>. My code accounts for file extension if there are any.

Solution below uses combination of regex with NumberFormat() function. Advantage of using NumberFormat() - no matter how many digit filename has in it, all will be listed in proper order.
 // simulate cfdirectory
 colvalues = listToArray("file10,file124.txt,file2,file23.xls,file3.txt");
 dirQuery = querynew("");
 queryaddcolumn(dirQuery, "name", colvalues);
<cfquery dbtype = "query" name="cfdir_query">
from dirQuery 
<cfset  finalQuery = queryNew("fileName,fileOrder")>
<cfloop query="cfdir_query">
  <cfset fileName_order = NumberFormat(REReplace(name, "^\D+(\d+)(\..*)?$", "\1"))>
  <cfset temp = QueryAddRow(finalQuery)>
  <cfset temp = QuerySetCell(finalQuery, "fileName", name)>
  <cfset temp = QuerySetCell(finalQuery, "fileOrder", fileName_order)>
<cfquery dbtype = "query" name="finalSort">
	select fileName, fileOrder
	from finalQuery 
	order by fileOrder
<cfdump var="#finalSort#">

Open in new window

I felt something was missing or wrong and returned back to your question. I re-read your original code - there is a contradiction. Inside your <cfdirectory> tag you have type attribute set to "file":
      <cfdirectory directory="#documentsDir#" name="dirQuery" listinfo="all" action="list" type="file">
But at the same time while looping through your query you are checking for type:
      <cfif dirQuery.type IS "dir">

There should not be directories returned by your <cfdirectory>, only files. To return directories it has to be: type="dir". To return both files and directories: type="all"

What's the purpose for building dirsArray? Are you displaying them below? Or you want to build hierarchy? If nothing like that, then you do not need to build dirsArray.

All you would need to modify in my code for example would be replace <cfscript> in my code with your:
   <cfset documentsDir = ExpandPath( "./meetingDocs/TechEnvMeeting11-20-08/" ) />
   <cfdirectory directory="#documentsDir#" name="dirQuery" listinfo="all" action="list" type="file">
and - at the end - get rid of <cfdump> in favor of your unordered list:
     <cfoutput query="finalSort">
        <li><a href="./meetingDocs/TechEnvMeeting11-20-08/#fileName#">#fileName#</a></li>
Have you sorted it out?
PhotoMan2000Author Commented:
Hi I sort of don't need this any more and am not sure what to do about the points.

Admin - please advise.  No need to refund them to my account.
since both eszaq and i provided working answers you should probably split the points rather than delete
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.