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">
, (firstName + ' ' + lastName) AS fullName
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)) />
<cfset querySetCell(getArtists, "fullName", uCase(getArtists.LASTNAME & ", " & getArtists.FIRSTNAME), getArtists.currentRow) />
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.
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?
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.
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.
Wednesday, March 18, 2015 at 9:44:01 AM Coordinated Universal Time
Agreed it is always better to do this in the original query...however that is not always possible.