Ask Ben: Changing ColdFusion Query Column Names

I want to change the names of the fields in a CFQUERY result set after I have retrieved the results from the database (ie. I can't use "AS" in the original SQL statement). I can think of 2 ways to do this - use the query-a-query (CFSQL) feature or just build and populate a new query object. Do you know which of these would be more efficient? And is there a better way, whereby I can manipulate the names of the columns in the original query (ie. so that I can rename the "CustName" field to "Name" and then use "CustList.Name" directly)?

Both ways you talk about are completely valid. Even more than that, they are both officially supported features of ColdFusion. What I am about to show you utilizes the underlying Java methods of the ColdFusion query object. These are not *supported* per say, but from everything I have been told, it is not crazy to use them.

The method that I would like to use to do this is called SetColumnNames(). This takes an array of string column names. The query will take the array and name each query column appropriately. Now here's the catch: it seems you can't rename just a single column AND the order of the column names in the array passed in has to line up with the internal order of the column in the underlying Java object.

If you look at what gets returned from Query.ColumnList, you will see that the list is always in alphabetical order. You might be tempted to use this order, however, you cannot. The "actual" order of the columns in the underlying structure does NOT change even when the column names are changed. Therefore, we have to get the actual columns from the query's underlying Java method: GetColumnNames(). This returns an array-like object of column names in their underlying order.

So, let's put that all together into a function: QueryChangeColumnName():

<cffunction

name="QueryChangeColumnName"

access="public"

output="false"

returntype="query"

hint="Changes the column name of the given query.">

<!--- Define arguments. --->

<cfargument

name="Query"

type="query"

required="true"

/>

<cfargument

name="ColumnName"

type="string"

required="true"

/>

<cfargument

name="NewColumnName"

type="string"

required="true"

/>

<cfscript>

// Define the local scope.

var LOCAL = StructNew();

// Get the list of column names. We have to get this

// from the query itself as the "ColdFusion" query

// may have had an updated column list.

LOCAL.Columns = ARGUMENTS.Query.GetColumnNames();

// Convert to a list so we can find the column name.

// This version of the array does not have indexOf

// type functionality we can use.

LOCAL.ColumnList = ArrayToList(

LOCAL.Columns

);

// Get the index of the column name.

LOCAL.ColumnIndex = ListFindNoCase(

LOCAL.ColumnList,

ARGUMENTS.ColumnName

);

// Make sure we have found a column.

if (LOCAL.ColumnIndex){

// Update the column name. We have to create

// our own array based on the list since we

// cannot directly update the array passed

// back from the query object.

LOCAL.Columns = ListToArray(

LOCAL.ColumnList

);

LOCAL.Columns[ LOCAL.ColumnIndex ] = ARGUMENTS.NewColumnName;

// Set the column names.

ARGUMENTS.Query.SetColumnNames(

LOCAL.Columns

);

}

// Return the query reference.

return( ARGUMENTS.Query );

</cfscript>

</cffunction>

As you can see, we have to both get and set the column names via the underlying Java record set. Notice that the function returns the query object itself. You don't have to do this. Since ColdFusion query objects are passed by reference, you don't have to return anything. I like to return the query object so that I can chain my methods:

<cfset QueryChangeColumnName(

QueryChangeColumnName(

qData,

"x",

"y"

),

"a",

"b"

) />

Notice that in one swoop, we change two columns (a to b and x to y). Returning the query object allows us the flexibility to chain our actions in this way.

Ok, but you asked about speed an efficiency. Well, this method above is gonna be the fastest thing to do as it doesn't manipulate the query data, just the meta data. But, to demonstrate this, I did some speed tests. As always, I had to start by building a query object:

<!--- Create the girls query. --->

<cfset qGirls = QueryNew(

"first_name, last_name",

"VARCHAR, VARCHAR"

) />

<!--- Add query rows. --->

<cfset QueryAddRow(

qGirls,

50000

) />

<!--- Build the query. --->

<cfloop

index="intI"

from="1"

to="#qGirls.RecordCount#"

step="1">

<!--- Set cell values. --->

<cfset qGirls[ "first_name" ][ intI ] = JavaCast(

"string",

"Julia"

) />

<cfset qGirls[ "last_name" ][ intI ] = JavaCast(

"string",

("Niles" & intI)

) />

</cfloop>

Notice that I am using JavaCast() so set the column values. Always an important thing to do for data integrity. Now, let's test the above query against the two other methods you suggested, the query of query and the query duplication:

<!--- Test are Java methods. --->

<cftimer label="QueryChangeColumnName" type="outline">

<!--- Change last name to given name. --->

<cfset QueryChangeColumnName(

qGirls,

"last_name",

"family_name"

) />

</cftimer>

<!---

Test the ColdFusion query of queries method in which we

alter the name of column by selecting it AS another name.

--->

<cftimer label="ColdFusion Query of Query" type="outline">

<cfquery name="qGirls2" dbtype="query">

SELECT

first_name,

(

family_name

) AS last_name

FROM

qGirls

</cfquery>

</cftimer>

<!---

Test the speed of manually building a completely new query

and just setting the new column to the old column value.

--->

<cftimer label="QueryNew()" type="outline">

<!--- Define the new query. --->

<cfset qGirls2 = QueryNew(

"first_name, last_name",

"VARCHAR, VARCHAR"

) />

<!--- Query records. --->

<cfset QueryAddRow(

qGirls2,

qGirls.RecordCount

) />

<!---

Loop over the original query and get at the data. We

are doing an indexed loop as opposed to a query loop

as the index loop has some speed advantages.

--->

<cfloop

index="intI"

from="1"

to="#qGirls.RecordCount#"

step="1">

<cfset qGirls2[ "first_name" ][ intI ] =

qGirls[ "first_name" ][ intI ]

/>

<cfset qGirls2[ "last_name" ][ intI ] =

qGirls[ "family_name" ][ intI ]

/>

</cfloop>

</cftimer>

As with anything in ColdFusion (which is such an awesome, kick ass language) with a small record/column set everything executes in 0 ms. To get a really good speed difference going, I pumped this up to 50,000 records. With this large record set, the QueryChangeColumnName() method executes instantaneously (0 ms). The ColdFusion Query of Queries method executes in about 220 ms on average. And, the manual building of a new query executes in a sluggish 1,100 ms on average.

So, not only was our QueryChangeColumnName() much faster (infinitely faster), it also is a better approach as it does not duplicate any information. Both the other methods have to actually touch the underlying data, and in both cases have to DUPLICATE the record set in some way. This is just going to take time and eat of system resources. The QueryChangeColumnName() method does not touch the query data, just the query meta data, which is always a small set of data.

I was a little surprised that the ColdFusion query of queries was faster than the manually built query. In my experience, query of queries is generally slower for duplicating a query. But I guess that just goes to show there is more to speed testing than simple iteration.

Reader Comments

Did the initial respondant ask you this directly, or did you just spot it on the CF forums @ Adobe. I ask because a seemingly verbatim issue has been raised there: http://tinyurl.com/yjrnec.

Either way, it might be useful to post your response to the forums too? Any help answering questions there would be appreciated! :-)

If you are - as I suspect - just finding interesting questions elsewhere and choosing to answer them on your blog (fair enough: an answer is an answer, and answers are good), it's a bit disingenuous to suggest someone is asking YOU this question, and you should perhaps cite where/how you came across the question in the first place.

When investigating this sort of thing myself a while back, I suggested to some Macromedia bods that they properly documented the "under the hood" methods available to the various Java classes they use for query objects and the like. They categorically said "NO WAY", the reason being they did not want to imply any contract that the methods exposed by those classes would necessarily be maintained in any way that would suggest backwards compatibility between CF versions. They're not really intended for popular usage.

I have seen these methods change behaviour between CF updaters (ie: not just point releases or major releases: updaters), and have accordingly steered clear of them for production code or anything more than really curio value.

This question was sent to me last night via my "Ask Ben / Contact Form". I don't make up questions, which is why i have large "dry spells" of answering... it's not that I don't have time, people just don't ask very much.

I have never been on the Adobe forums before. I try to keep the stuff I have to concentrate on smaller. That is why I pretty much stick to full as a good and feed-squirrel.

As far as accessing Java methods, as I posted in the entry, I have gone back a forth with Ben Forta a bit and he suggested that using String methods was totally fine, but the rest was questionable (but not totally bad as the response that you got).

As far as citing who I got the question from, this is my methodology. I get the question. I try to answer it the best that I can. I post to the site AND I email the person a link to the answer. Then, if they choose, they can leave the "Thank you" as a comment and identify themselves.

Most of the time people don't identify themselves, and I don't care enough to ask them if they care. I just like answering stuff :)

That was awesome and just what I needed. I'm trying to implement Verity Search with the cfgrid type=html AJAX feature. Verity limits field names and thus I used it to change "CUSTOM1..." to my grid column names. Worked like a charm.

Have you messed with Verity much? If so, have you tried to post results to a cfgrid? I seem to recall your name in a post when I was searching for answers related to Verity, but it may have been anti-Verity.

Here, we are changing the column "b" to be "new_b". Of course, there is a lot more overhead to do this (since it has to execute a query), but this will work with any engine that supports query of queries.

I have not (although I haven't tried this approach in a while). I feel like I was just talking to someone last week or the week before about using this with success; and part of that was that it handled NULL values with success.

Does the column have a default value in the schema? Not that would necessarily make a difference. I just don't really have any other insight.

I have a theory that it might be some kind of QofQ crazyness and that the datatypes of the fields might be changing as I tweak the query manually. I'll do some more in depth experimentation and see what I can figure out.

A few days after finding this I had a need to delete a query column. One way would be using a query of query. The much better way is to change 1 line in your function from: LOCAL.Columns[ LOCAL.ColumnIndex ] = ARGUMENTS.NewColumnName;

to: arrayDeleteAt(LOCAL.Columns, LOCAL.ColumnIndex);

The column with its data obviously still exists but cannot be accessed because the reference to it has been removed.

I am the co-founder and lead engineer at InVision App, Inc — the world's leading prototyping,
collaboration & workflow platform. I also rock out in JavaScript and ColdFusion 24x7 and I dream about
promise resolving asynchronously.