ColdFusion Query Maintains Current Row Even When Passed By Reference
Posted February 28, 2007 at 6:31 PM by Ben Nadel
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:
- <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.
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.
@Daniel,
Exactly!
Hmmm (;
Yamato
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.
@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.
It seems that currentRow is not being carried over if you pass a query to a function in a different object.
@Shawn,
That's interesting. I wonder why that would be.
Sweet, worked perfectly! Thanks again for doing all the dirty work, Ben :P
@Jon,
Always glad to help :)
This worked great, thanks for the info.
@Gerry,
Thanks my man. And actually, your comment prompted me to try something else along these same lines. In the example, I'm using a .currentRow property; however, the current row state of the query is maintained implicitly. Take a look at this:
- <!--- Build a query. --->
- <cfset girls = queryNew( "" ) />
- <!--- Add a column with some default values. --->
- <cfset queryAddColumn(
- girls,
- "name",
- "cf_sql_varchar",
- [ "Sarah", "Tricia", "Joanna" ]
- ) />
- <!---
- A function that returns the NAME of the query
- using the implicit row.
- --->
- <cffunction name="getName">
- <cfreturn arguments[ 1 ].name />
- </cffunction>
- <!--- Try to output each name using the proxy function. --->
- <cfoutput>
- <cfloop query="girls">
- #getName( girls )#<br />
- </cfloop>
- </cfoutput>
When I run this, I get the following output:
Sarah
Tricia
Joanna
As you can see, not only is .currentRow maintained, so is the implicit iterator that allows us to reference column values without any row number.
This is beautiful.
But I'm having trouble with the syntax for calling the function if it's in a external cfc.
Wouldn't it be something like
<cfset createObject("cfc.NAME").OutputQueryRow(query_name)>
????
Great post !
I've tried this with with a query-loop in cfscript:
- //myQuery has 2 rows. column 'id' is 1 in row 1, and 2 in row 2
- while(myQuery.next())
- {
- writeOutput("<br />#myQuery.currentRow#: ");
- outputCurrentRow(myQuery);
- }
- public void function outputCurrentRow(Query myQuery){
- writeOutput(arguments.myQuery.id);
- }
this outputs:
1: 1
2: 1
In this case the current row remained the first row of the query. The output for each row was the id of row 1.
Thanks Ben! Great post. I've been making this process way too complicated... thank you for simplifying!
