Ben Nadel © 2008.
All content is the property of Ben Nadel and Kinky Solutions.
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:
Launch code in new window » Download code as text file »
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:
Launch code in new window » Download code as text file »
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.
Launch code in new window » Download code as text file »
... 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:
Launch code in new window » Download code as text file »
It gets even stranger when this:
Launch code in new window » Download code as text file »
... 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.
Download Code Snippet ZIP File
Comments (5) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
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.)
Posted by Rick O on Aug 2, 2006 at 8:07 PM
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 :/
Posted by Nath on Jan 23, 2008 at 10:45 AM
@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.
Posted by Ben Nadel on Jan 23, 2008 at 11:03 AM
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!
Posted by nath on Jan 23, 2008 at 12:22 PM
Since it's a query now you should just be able to query it using cfquery and simple SQL.
Posted by Chad on May 20, 2008 at 3:46 PM