I've run into very few instances where the alpha sorting of query columns
has been a bother. However, I just ran into one yesterday.
I wanted to use CFTABLE as a quick and easy way to display an unknown
recordset. Using COLUMNLIST from the CFQUERY result wouldn't work - I wanted
the 'natural' order of my columns.
I found a nifty little method in the query that allowed me to gain that
information: GetColumnNames()
<cfset ColNameArray = Query.GetColumnNames()>
<cfset ColNameList = ArrayToList(ColNameArray)>
>From there, I was able to loop over the list and generate my CFTABLE in the
correct order.
Now, this doesn't restructure the recordset, but rather gives you access to
the proper order of column names. Hopefully this will be helpful towards
whatever you're trying to accomplish.
Oh, and for the record - the other time alpha sorting irks me is when I want
to do a quick CFDUMP to see the raw data. Obviously not important to the end
application, but it annoys me :)
Rob
-----Original Message-----
From: Todd Mathews
Sent: Thursday, September 20, 2007 3:08 AM
To: CF-Talk
Subject: CFQUERY Results in Natural SQL Order?
Does anyone know of a simple way to have the results of a CFQUERY return the
results in the natural sql result order instead of always returning
everything in a sorted column order?
IE:
Select waypointName, areaName, directionScope, bufferName from xx
Should return the columns in the natural order of the sql query (in this
case waypointName,areaName, directionScope,bufferName) instead of the
current default alphanumeric column sorting which returns the columns using
an alphabetical sort of (areaName,bufferName,directionScope,waypointName).
We're currently using the following approach to restore the natural order
but would LOVE to find a more simplified solution.. In a perfect world this
would be a param option for CFQUERY (think something like resultOrder='NAT'
or resultOrder='ALPHA' hopefully this conveys the general idea):
<!--- use getMetadata to get the names and data types of the fields --->
<cfset querymeta=getMetaData(getRecs)>
<!--- build colList and field name / type query --->
<cfset thisColList = "">
<cfset queryInfo=QueryNew("colName,colType")>
<cfloop index="i" from="1" to="#arrayLen(querymeta)#">
<cfset thisColName = querymeta[i].name>
<cfset thisColType = UCase(querymeta[i].TypeName)>
<cfset thisColList = listAppend(thisColList,thisColName)>
<cfset tmp=QueryAddRow(queryInfo)>
<cfset tmp=QuerySetCell(queryInfo,"colName",thisColName)>
<cfset tmp=QuerySetCell(queryInfo,"colType",thisColType)>
</cfloop>
The above code restores the natural SQL result order and gets the job done
but a more simple solution would be quite welcome.
Anyone have any thoughts or no any secrets?
Todd
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:288907
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
