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 the NYC Tech Talk Meetup (Aug. 2010) with:

ColdFusion Query Maintains Current Row Even When Passed By Reference

By Ben Nadel on
Tags: ColdFusion

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.

Reply to this Comment

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.

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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

Reply to this Comment

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.

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.