Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at cf.Objective() 2013 (Bloomington, MN) with: Ryan Anklam

Calling Array Functions on ColdFusion Query Columns

By Ben Nadel on
Tags: ColdFusion

For those of you who don't follow the House of Fusion CF-Talk list, Chris Peterson just posted a message about running ColdFusion array methods on query columns. This is new for me, I had no idea that you could do this. It is very exciting (I agree). However, after some brief testing, I have found there to be a few caveats.

Let's assume we have the following query:

  • <!--- Build a new query with name and weight. --->
  • <cfset qGirls = QueryNew( "name, weight" ) />
  •  
  • <!--- Add some rows to the query. --->
  • <cfset QueryAddRow( qGirls ) />
  • <cfset qGirls[ "name" ][ 1 ] = "Jessica" />
  • <cfset qGirls[ "weight" ][ 1 ] = "129.5" />
  •  
  • <cfset QueryAddRow( qGirls ) />
  • <cfset qGirls[ "name" ][ 2 ] = "Marci" />
  • <cfset qGirls[ "weight" ][ 2 ] = "172.0" />
  •  
  • <cfset QueryAddRow( qGirls ) />
  • <cfset qGirls[ "name" ][ 3 ] = "Heather" />
  • <cfset qGirls[ "weight" ][ 3 ] = "104.0" />

Now, let's say you want to get the sum, average, or other type of aggregate of the weights. You could do so by passing in the ColdFusion query column to any array function like so:

  • <!--- Get the sum of weights. --->
  • <cfset flWeightSum = ArraySum( qGirls["weight"] ) />
  •  
  • <!--- Get the max weight. --->
  • <cfset flMaxWeight = ArrayMax( qGirls["weight"] ) />
  •  
  • <!--- Get the min weight. --->
  • <cfset flMinWeight = ArrayMin( qGirls["weight"] ) />
  •  
  • <!--- Get the average weight. --->
  • <cfset flAvgWeight = ArrayAvg( qGirls["weight"] ) />

This works really well and is a fairly neat feature. But look at how I am sending in the column reference; I am using array notation. For some reason, you CANNOT refer to the column like a regular structure in this case.

  • <!--- Get the sum of weights. --->
  • <cfset flWeightSum = ArraySum( qGirls.weight ) />

... Throws the error "Object of type class java.lang.String cannot be used as an array". This is a bit strange when you think about the function ValueList() which returns a comma-delimited list of values. ValueList() can take that structure-esque sort of column reference:

  • <!--- Get a list of weights. --->
  • <cfset lstValues = ValueList( qGirls.weight ) />

It gets even stranger when this:

  • <!--- Get a list of weights. --->
  • <cfset lstValues = ValueList( qGirls["weight"] ) />

... throws an error! Try and run that line of code and you get "Invalid CFML construct found on line X at column Y. ColdFusion was looking at the following text: [".

So, it seems that ColdFusion methods that are "designed" to deal with query columns can take their reference as QUERY.COLUMN but ColdFusion array methods that are "designed" to handle straight up arrays can only handle the reference as QUERY["COLUMN"]. Still, quite the cool little tip, thanks Chris.

Note: I tried to use column types VARCHAR and DECIMAL in my QueryNew() call; it did not help.



Looking For A New Job?

100% of job board revenue is donated to Kiva. Loans that change livesFind out more »

Reader Comments

Remember that #query.column# is short-circuited in CF to actually mean #query.column[query.currentrow]#. (Which is why you can just do #query.column# right after fetching a query, but before looping over it, and get the values of the first row of the query.)

So I guess that the Allaire/Macromedia/Adobe folks were smart enough to ensure that #query.column# is short-circuited correctly, but didn't think about #query["column"]#. (Or just left it as an Easter Egg. Whatever.)

Reply to this Comment

You can duplicate columns directly into arrays as well using this syntax:

contents_array=duplicate(a_query["the_column"]);

this will preseve the empty cells of a column that would otherwise be ignored using:

contents_array=listtoarray("#valueList(a_query.the_column)#");

BUT (always is) the first item in the column is ignored in the duplicate method. very strange :/

Reply to this Comment

@Nath,

That is some cool stuff. I will take a look at that. My guess, with the first element, is that in Java, it is zero-based, but in ColdFusion, it is one-based, so the duplication method might not be checking index zero. Cool tip, though.

Reply to this Comment

another line sorts it -

the_array=duplicate(the_query['column_name']);
ArrayPrepend(the_array,the_query['column_name'][1]);

i've got it in a CFC function so i can call it when needed - i guess it could be a udf too.

Keep up the good work your website has been a great source of info for me over the years!

Reply to this Comment

Since it's a query now you should just be able to query it using cfquery and simple SQL.

Reply to this Comment

I get very different results when I try using this technique across MX6.1 and MX 7 on my dev server.

The difference is the value returned from duplicate(myquery['columname'])

In MX6.1 it returns an array of the column missing the first value as expected. However in MX7 it returns a string containing the first value only. This is very different behaviour and means that the neat duplicate() then arrayAppend() techinque fails.

That's a right pain as it's such a neat technique.

Am I going mad?
Has anyone else tried?

Reply to this Comment

Sorry, typo in my previous post. arrayAppend() should of course have been arrayPrepend().
Problem with duplicate() still stands though...

Reply to this Comment

Yep in CF 7 it fails - i ended up changing my code to do this:

the_array=arraynew(1);
for(i=1;i lte the_query.recordcount;i=i+1){
the_array[i]=the_query[column_name][i];
}

not as neat, and probably slower - but it worked at the time and keeps the boss happy...

If you find a way of getting the original one to work again or another method leave a comment here for us.

Nath

Reply to this Comment

I've not found a way to make it work in mx7 so I ended up writing a UDF to encapsulate it. That way if I find a compatible faster way in the future I can change the innards... :o)

My udf is just the standard loop like your example. Such a shame the the duplicate method doesn't work...

<code>
<!---
This is the bog standard way of achiving this.
It's probably not the fastest but it works consistantly in all versions.
can't use duplicate(myquery[colname]) in mx7
--->
<cffunction name="queryColumnArray" returntype="array" description="Returns an array of a given queries named column.">
<cfargument name="qry" type="query" required="true" hint="the query">
<cfargument name="col" type="string" required="true" hint="the column name">
<cfset var aRtn = arrayNew(1)>
<cftry>
<cfoutput query="arguments.qry">
<cfset arrayAppend(aRtn, arguments.qry[arguments.col][arguments.qry.currentRow])>
</cfoutput>
<cfcatch>
<cfoutput>#cfcatch.Message# : #cfcatch.Detail#</cfoutput>
</cfcatch>
</cftry>
<cfreturn aRtn>
</cffunction>
</code>

Reply to this Comment

Some advice please - I am trying to use an array to populate an insert into statement with the values I need stored in a database.

The code looks like this:

<cfset myArray = ArrayNew(1)>
<cfloop query="GetList">
<cfoutput>#ArrayAppend(myArray, "('#thename#', '#theemail#')")#</cfoutput>
</cfloop>
<cfset THESQL = ArrayToList(myArray, ", ")>

<cfquery name="AddAdmin" datasource="#datasource#" username="#DBuser#" password="#DBpassword#">
INSERT INTO email_temp ( name, email )
VALUES #THESQL#;
</cfquery>

The problem I have found is that when CF outputs the array to the MySQL statement it surrounds the array values with extra quotations for example the MySql statement looks like this:

INSERT INTO email_temp ( name, email ) VALUES ('', ''malcolm@horizon.co.zw''), ('', ''sharon@horizon.co.zw''), ('', ''traceydare@avimo.com''), ('', ''ychristiansson@unicef.org''), ('', ''Sarah.Sargent@diageo.com''), ('', ''johans@fairfieldtours.com''), ('', ''michelle@jgcons.co.zw''), ('', ''mukota@cairnsfoods.co.zw'')

Why is it doing this and how can I stop it!?

Thanks

Reply to this Comment

Sorry Dave, I can't help but say that.

I think what you need to do is use the PreserveSingleQuotes() function [http://www.cfquickdocs.com/?getDoc=PreserveSingleQuotes#PreserveSingleQuotes]. It's designed specifically for the task.

Also in your loop you don't need to put the arrayappend() in a <cfoutput> block, just use a <cfset> with no variable on the left. Doesn't make any functional difference (I think) but is cleaner and easier to read.

<cfloop query="GetList">
<cfset ArrayAppend(myArray, "('#thename#', '#theemail#')")>
</cfloop>

Reply to this Comment

Thanks Mat! Sorry if my comment was off topic, but I needed some advice - I live in Zimbabwe and am the only CF almost programmer I know.

Reply to this Comment

@Dave,

No worries - there's never a wrong place to start a good conversation :)

As far as the issue, @Matt is correct. By default, ColdFusion escapes single quotes in variables evaluated inside of CFQuery tags.

Reply to this Comment

Thanks Ben. Needed to remember how to do this and, as always, you help was spot on. :)

Reply to this Comment

What if you formed the array girls[girl_id][weight]. How would you get the averages of the weight?

Reply to this Comment

@Kate,

Hmm, I am not sure if the methods work with multi-dimensional arrays. It might give you the average of their IDs.

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.