As of ColdFusion 10, the "Query" attribute of the CFLoop tag can now be dynamic. That is, it can refer to a dynamic expression rather than a static name (string). At first, this seems like a really cool idea - one that you've probably even wanted over the years; but, don't do it. Using a dynamic query value forces you to, as far as I can tell, use unscoped query column values. Granted, this is just my personal opinion but, I feel very strongly that this is a bad idea.
NOTE: At the time of this writing, ColdFusion 10 was in public beta.
Before ColdFusion 10, if you wanted to use a dynamic query value in the CFLoop tag, you'd have to store the query reference in an intermediary variable before the CFLoop tag was invoked. Then, the name of the intermediary variable would have to be used in the Query attribute of the CFLoop tag. With ColdFusion 10, this is no longer a requirement - now, you can put your dynamic query expression directly in the CFLoop tag attributes:
<cfoutput> <!--- Build up a query. ---> <cfset friends = queryNew( "id, name", "cf_sql_integer, cf_sql_varchar", [ [ 1, "Tricia" ], [ 2, "Joanna" ], [ 3, "Sarah" ] ] ) /> <!--- This is just a helper function. ---> <cffunction name="getQuery"> <cfreturn friends /> </cffunction> <!--- Loop over the query returned from the getQuery() function. Notice that the query value no longer has to be static. ---> <cfloop query="#getQuery()#"> [ #id# ] #name#<br /> </cfloop> </cfoutput>
Notice that the query attribute of my CFLoop tag is using the expression, getQuery(). This gets the query from the given function and pushes it onto the query scope of the CFLoop tag. When we run the above code, we get the following page output:
[ 1 ] Tricia
[ 2 ] Joanna
[ 3 ] Sarah
This seems really awesome; but, if you look at the column references within the CFLoop tag body, you'll notice that they are all unscoped. This is because our dynamic query value is not attached to any variable name that we can access (as far as I can tell). This creates ambiguous references and prevents us from being able to augment the query object.
Trust me, this will come back to bite you. Your query column references should always be explicitly scoped.
As a fun experiment, I wanted to see if I could use the setVariable() function as a way to combine the dynamic query expression with the creation of an intermediary variable that would afford column scoping. As I mentioned six years ago, the setVariable() function is one of the ways that ColdFusion allows dynamic variable assignment. In most cases, setVariable() is not the right choice, but in this case, it's one of the few things that will compile.
NOTE: Using evaluate() in the query value will also compile and function properly.
<cfoutput> <!--- Build up a query. ---> <cfset friends = queryNew( "id, name", "cf_sql_integer, cf_sql_varchar", [ [ 1, "Tricia" ], [ 2, "Joanna" ], [ 3, "Sarah" ] ] ) /> <!--- This is just a helper function. ---> <cffunction name="getQuery"> <cfreturn friends /> </cffunction> <!--- Loop over the query returned from the getQuery() function. Notice that we're not just passing-in the dynamic query value; rather, we're assigning it to an intermediary variable, peope, which is then passed onto the CFLoop tag. ---> <cfloop query="#setVariable( 'people', getQuery() )#"> [ P #people.id# ] #people.name#<br /> </cfloop> </cfoutput>
In this demo, we're getting the dynamic query value from getQuery(), assigning it to the intermediary variable, "people," and then passing it onto the CFLoop tag. By doing this, it allows us to scope the query column values with the prefix, "people". This allows us to implement the best-practices approach to referencing query columns.
When we run the above code, we get the following page output:
[ P 1 ] Tricia
[ P 2 ] Joanna
[ P 3 ] Sarah
As you can see, this works, dynamica query assignment and all.
I'm not necessarily advocating this latter approach; I'm not necessarily saying it's bad, either. The only point of this post is to caution heavily against using any approach that requires query column values to be referenced without explicit scoping.
End rant :)
Want to use code from this post? Check out the license.