Dynamically Appending A Column To A Query In ColdFusion

Author: Steven Neiland
Published:

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

Since my life has been somewhat how do I say this... crazy the last few months I've not had much time for blogging. I'm going to try rectify that and get back in the habit. To start off though I'm going to cheat just a little bit by finishing up a draft I've had sitting around for a couple of years now.

Premise: How do you add an extra column to an existing query?

Wrong Way: Using Query of Query

I see this a lot and it does work.

<cfquery name="getArtistWithNewCol" dbtype="query">
      SELECT
            firstName
            , lastName
            , (firstName + ' ' + lastName) AS fullName
      FROM getArtists
</cfquery>

The problem I see with this though is that it requires starting up the QoQ engine which if not already started takes time.

Right Way: Using QueryAddColumn()

A better way of doing this is to just manipulate the query object directly like this.

<cfset queryAddColumn(getArtists, "fullName", ArrayNew(1)) />
<cfloop query="getArtists">
      <cfset querySetCell(getArtists, "fullName", uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME), getArtists.currentRow) />
</cfloop>

Now the results are essentially the same and the performance difference is minimal and getting smaller on newer versions of CF, but I still hold that the second option is better.

Reader Comments

Seth Krosich's Gravatar
Seth Krosich
Tuesday, March 17, 2015 at 11:25:10 AM Coordinated Universal Time

Is that better than building an array with the full name values and then using queryAddColumn to add the array with all of the values at once?

Steven Neiland's Gravatar
Steven Neiland
Tuesday, March 17, 2015 at 2:12:44 PM Coordinated Universal Time

Its really the same thing. Either way you need to loop over the query to create a full name.

Marc Ackermann's Gravatar
Marc Ackermann
Wednesday, March 18, 2015 at 4:50:18 AM Coordinated Universal Time

I would always try to do this in the original sql query so the database server has to process this and not via cf code / AddColumn / array building/ looping etc.

Steven Neiland's Gravatar
Steven Neiland
Wednesday, March 18, 2015 at 9:44:01 AM Coordinated Universal Time

Marc,
Agreed it is always better to do this in the original query...however that is not always possible.

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing