Using A Dynamic Column Name With ValueList() In ColdFusion
For the most part, ColdFusion is awesome when it comes to creating and consuming dynamic variables. Using quotes, we can easily create dynamic variable handles; we can define dynamic class paths; we can define dynamic struct keys; and, we can access dynamic struct keys. ColdFusion even provides ways to invoke dynamic methods on components and determine the name of the functions invoked under aliases... and that's just the tip of the iceberg. One oversight in the language that has always bothered me, however, was the non-dynamic nature of the valueList() function. Fortunately, you can bridge this gap through the use of evaluate().
In ColdFusion, the valueList() function takes a reference to a query column and returns the column values as a delimited list. The query column reference must be passed into the valueList() function in the form of "query.column", as in:
valueList( users.id );
If you try to use array-notation to reference the column:
valueList( users[ "id" ] );
... you'll get the following ColdFusion error:
Complex constructs are not supported with function valueList. Use simple variable instead.
If you try to create an intermediary value with the dynamic column name:
column = users[ "id" ];
valueList( column );
... you'll get the following ColdFusion error:
The ValueList() function has an invalid parameter: column. Parameter 1 of function ValueList which is now column must be pointing to a valid query name.
It seems that the only solution to using dynamic column names with valueList() in ColdFusion is to use the evaluate() function. Luckily, this is quite easy:
<cfscript>
friends = queryNew( "" );
// Add our name column with initial row values.
queryAddColumn(
friends,
"name",
"cf_sql_varchar",
[ "Sarah", "Tricia", "Jill" ]
);
// Create a variable that holds our "dynamic" column reference.
targetColumn = "name";
// Use the evaluate() function to invoke the valueList() function
// using our dynamic column reference.
writeDump(
evaluate( "valueList( friends.#targetColumn# )" )
);
</cfscript>
Notice that our valueList() function is still receiving an argument in the form of "query.column"; only, the code being executed is dynamically generating the argument structure being passed-in. In essence, we moved the "dynamic" nature up one level, from the invocation to the compilation.
Running the above code outputs the following content:
Sarah,Tricia,Jill
I know that a lot of people (myself included) have historically shied away from the evaluate() function; especially since it is often misused, in lieu of available core functionality. However, when it comes to the valueList() function in ColdFusion, evaluate() is the easiest way, that I can think of, when it comes to passing-in dynamic column references.
Want to use code from this post? Check out the license.
Reader Comments
@Ben - I believe you can achieve the same functionality with ColdFusion's built in ArrayToList() function.
ArrayToList( users[ "id" ] );
@Josh,
Oh snap! You're totally right! I'm not sure I've ever tried that. I did know that you can call a number of other array-methods on ColdFusion query columns:
www.bennadel.com/blog/167-Calling-Array-Functions-on-ColdFusion-Query-Columns.htm
... but I don't think arrayToList() ever occurred to me. Rockin!
I thought when you used array notation to reference queries you always had to have the row or it would throw a similar error as well?
@Dana,
That's if you're trying to reference a specific row. In this case, we're trying to reference the entire query column as one cohesive value. So, you are correct that if you wanted to output a value, you would need something like this (in CFScript):
query.column[ rowNumber ]
I was thinking chicken and the egg, I wouldn't have expected it to work in the valuelist going in I guess. Maybe I just need a beer, long day :)
@Dana,
Your confusion is well founded, since this is a very confusing features. In fact, it ONLY works if you use array notation. Meaning, that this:
arrayToList( query[ "columnName" ] );
... is NOT the same as:
arrayToList( query.columnName );
ColdFusion allows you to treat a query column as an array IF / ONLY IF you use the array notation. Check out this blog post for some more details:
www.bennadel.com/blog/167-Calling-Array-Functions-on-ColdFusion-Query-Columns.htm
... now, this gets even more confusing when the function valueList() works in the opposite direction - it requires dot-notation and will NOT reference the query column as an array if you use array-notation.
Enough to make yer head spin ;)
Once again, Ben, you've provided a straight forward solution to a problem that could have caused me all kinds of headaches.
Thanks!