ColdFusion Query Maintains Current Row Even When Passed By Reference
Posted February 28, 2007 at 6:31 PM
I thought about this in the shower this morning (I take showers in the dark - it helps me think). I know that a ColdFusion query object knows what row it is in while being used in a CFLoop, that's obvious. But what happens if a ColdFusion query object is passed to another function in the middle of a CFLoop. Does it maintain the current row? After some quick testing, I can say that's a BIG 10-4, buddy!
I can't believe I never tested this before. This is something that would have been good to know. Why pass around a row index if the Query object is keeping track of it for you?!? To test this, I set up a little function that outputs the data for the current row of the passed in Query object:
Launch code in new window » Download code as text file »
- <cffunction
- name="OutputQueryRow"
- access="public"
- returntype="void"
- output="true"
- hint="Outputs the current row of the passed in query.">
-
- <!--- Define arguments. --->
- <cfargument
- name="Query"
- type="query"
- required="true"
- hint="The query whose row data we are outputting."
- />
-
- <cfargument
- name="Row"
- type="numeric"
- required="false"
- default="#ARGUMENTS.Query.CurrentRow#"
- hint="The row we are going to output (defaults to current row of the passed in query)."
- />
-
- <!--- Set up local scope. --->
- <cfset var LOCAL = StructNew() />
-
- <!---
- Loop over each query column and output both the
- column name and cell value (in this row only).
- --->
- <cfloop
- index="LOCAL.ColumnName"
- list="#ARGUMENTS.Query.ColumnList#"
- delimiters=",">
-
- #LOCAL.ColumnName# :
- #ARGUMENTS.Query[ LOCAL.ColumnName ][ ARGUMENTS.Row ]#<br />
-
- </cfloop>
-
- <!--- Return out. --->
- <cfreturn />
- </cffunction>
-
-
-
- <!--- Create the test query. --->
- <cfset qHottie = QueryNew( "" ) />
-
- <!---
- Now that we have the query, which is currently very blank,
- we are going to add the test columns to it.
- --->
- <cfset QueryAddColumn(
- qHottie,
- "name",
- "CF_SQL_VARCHAR",
- ListToArray(
- "Kit,Izzy,Laura,Samantha"
- )
- ) />
-
- <cfset QueryAddColumn(
- qHottie,
- "best_asset",
- "CF_SQL_VARCHAR",
- ListToArray(
- "Arms,Smile,Legs,Personality"
- )
- ) />
-
-
- <!--- Loop over the hottie query. --->
- <cfloop query="qHottie">
-
- <h4>
- Iteration #qHottie.CurrentRow#
- </h4>
-
- <p>
- <!---
- Output the row values. Notice that we are
- passing in the query ONLY and not telling
- it what row to use.
- --->
- <cfset OutputQueryRow( qHottie ) />
- </p>
-
- </cfloop>
You will notice that the ColdFusion user defined function (UDF) takes the query object as well as an optional row index argument. But also notice that I am NOT passing in this row index when invoking the method. Now, take a look at the row index default value; it's defaulting to the CurrentRow value of the passed in query argument. Very slick.
Running the above code gives us:
Iteration 1
BEST_ASSET : Arms
NAME : Kit
Iteration 2
BEST_ASSET : Smile
NAME : Izzy
Iteration 3
BEST_ASSET : Legs
NAME : Laura
Iteration 4
BEST_ASSET : Personality
NAME : Samantha
I love ColdFusion. It's too freakin' cool!
Now, this won't work with a CFScript / FOR loop. The FOR loop inside a script tag does not alter the ColdFusion query object - it merely accesses it by index. This will only work with a CFLoop tag that alters the state of the query object itself. Of course, using the UDF above, you could just pass in the alternate second row index argument if it is being called from within CFScript tags.
Download Code Snippet ZIP File
Comments (5) | Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Searching Directories And File Content Using ColdFusion
QueryAddRow(), Date/Times, WDDX, And ORDER BY, OH MY!
Reader Comments
Sounds like a solution for nested query loops as well. Access the outter loop row from the inner loop with array notation using outterQuery.currentrow as row number.
Posted by Daniel Roberts on Feb 28, 2007 at 10:11 PM
@Daniel,
Exactly!
Posted by Ben Nadel on Mar 1, 2007 at 7:21 AM
Hmmm (;
Yamato
Posted by exvego@excile.co.jp on May 29, 2007 at 9:29 PM
I've often wanted to work on query rows in custom tags, but today I needed to use a function. I just finished writing out <cfargument name="row"... when I found this post :)
One reason you might want to pass in the row is if you're doing odd/even logic or something that doesn't run in order (for whatever reason), then maybe you'd be better off with myFunction(query, row).
Thanks.
Posted by Adrian Lynch on Apr 8, 2008 at 8:01 AM
@Adrian,
Agreed. I think that's why its a good compromise to allow an optional Row argument, but default it to the CurrentRow. Best of both worlds.
Posted by Ben Nadel on Apr 8, 2008 at 9:32 AM
