Calling Array Functions on ColdFusion Query Columns
Posted July 25, 2006 at 3:32 PM by Ben Nadel
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.
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.)
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 :/
@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.
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!
Since it's a query now you should just be able to query it using cfquery and simple SQL.
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?
Sorry, typo in my previous post. arrayAppend() should of course have been arrayPrepend().
Problem with duplicate() still stands though...
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
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>
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
I'm sorry Dave, but I'm afriad I can't do that.
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>
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.
@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.
Thanks Ben!
I used this in SQL Zen Garden #003!
http://www.cfmzengarden.com/SQLZenGarden/003/
@Phillip,
Nice!
Thanks Ben. Needed to remember how to do this and, as always, you help was spot on. :)
@JV,
Glad to be here.
What if you formed the array girls[girl_id][weight]. How would you get the averages of the weight?
@Kate,
Hmm, I am not sure if the methods work with multi-dimensional arrays. It might give you the average of their IDs.
