?
Solved

Sort directory listing with coldfusion

Posted on 2008-11-13
14
Medium Priority
?
374 Views
Last Modified: 2013-12-24
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?

Thanks.
<!--- 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]=dirQuery.name>
    <cfset i = i + 1>
</cfif>
</cfloop>
<cfquery dbtype="query" name="finalSort">
select * from dirQuery order by dirQuery.name asc
</cfquery>
<ul>
<cfoutput query="finalSort">
        <li><a href="./meetingDocs/TechEnvMeeting11-20-08/#name#">#name#</a></li>
	</cfoutput>
   </ul>

Open in new window

0
Comment
Question by:PhotoMan2000
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22953551
try this
select name from dirQuery order by name
0
 

Author Comment

by:PhotoMan2000
ID: 22953632
No change in the order. Also tried:
select dirQuery.name from dirQuery order by dirQuery.name

0
 
LVL 36

Expert Comment

by:SidFishes
ID: 22953637
this is expected order by behaviour

order by looks at chars like this

file
   1, 10,
   2, 21,
   3

I'll see if i can knock together something that works for you
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:PhotoMan2000
ID: 22953675
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!
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 22954620
this is an issue with QoQ's (and is a lot more complicated than I first thought!)

0
 

Author Comment

by:PhotoMan2000
ID: 22954645
OK. is there any sort of solution or do I have to suffer with it? :)
0
 
LVL 36

Accepted Solution

by:
SidFishes earned 1000 total points
ID: 22954823
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
http://www.adobe.com/cfusion/exchange/index.cfm?event=extensionDetail&loc=en_us&extid=1000158#



<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 
</cfquery> 
 
 
 
<!--- create an array --->
	<cfset ord = arraynew(1)>
<cfloop query="test">
	
<cfset  arrayAppend(ord,
			#right(test.col1,len(test.col1)-evaluate(refindNocase("[0-9]",test.col1,0)-1))#)>
<!--- find the numbers in each file so we can sort on that --->
</cfloop>
<!--- append array to query --->
<cfset queryaddcolumn  (dirQuery, "Col2", "CF_SQL_integer", ord)>
 
<cf_querySort query="dirQuery" SortColumn="col2" sortorder="asc" sorttype="numeric"
				sortedquery="final">
<!--- sort on the column of numbers we added --->
<cfdump var="#dirQuery#">
<cfdump var="#final#">

Open in new window

0
 
LVL 8

Expert Comment

by:eszaq
ID: 23031779
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.
<cfscript>
 // simulate cfdirectory
 colvalues = listToArray("file10,file124.txt,file2,file23.xls,file3.txt");
 dirQuery = querynew("");
 queryaddcolumn(dirQuery, "name", colvalues);
</cfscript>
 
<cfquery dbtype = "query" name="cfdir_query">
select dirQuery.name 
from dirQuery 
</cfquery> 
 
<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)>
</cfloop>
 
<cfquery dbtype = "query" name="finalSort">
	select fileName, fileOrder
	from finalQuery 
	order by fileOrder
</cfquery> 
<cfdump var="#finalSort#">

Open in new window

0
 
LVL 8

Assisted Solution

by:eszaq
eszaq earned 1000 total points
ID: 23031985
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:
   <ul>
     <cfoutput query="finalSort">
        <li><a href="./meetingDocs/TechEnvMeeting11-20-08/#fileName#">#fileName#</a></li>
      </cfoutput>
    </ul>
0
 
LVL 8

Expert Comment

by:eszaq
ID: 23081407
Have you sorted it out?
0
 

Author Comment

by:PhotoMan2000
ID: 23213203
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.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 23444948
since both eszaq and i provided working answers you should probably split the points rather than delete
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question