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 Scotch On The Rocks (SOTR) 2011 (Edinburgh) with:

Using A Dynamic Column Name With ValueList() In ColdFusion

By Ben Nadel on
Tags: 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.




Reader Comments

@Ben - I believe you can achieve the same functionality with ColdFusion's built in ArrayToList() function.

ArrayToList( users[ "id" ] );

Reply to this Comment

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?

Reply to this Comment

@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 ]

Reply to this Comment

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 :)

Reply to this Comment

@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:

http://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 ;)

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.