Strange Behavior When Working with Nested Query Objects

Report · Feb 12, 2014

I just posted a comment over on Ben Nadel's site about this issue but I wanted to get some feedback from my fellow ColdFusion developers to see if anyone else has ever experienced this issue:

I have a function within a CFC that creates a query object and then nests another query object inside it (masterQuery > subQuery). The CFC returns the query object and to make things easier I loop through the query columns and bring everything into the local VARIABLES scope using the following format: "#a#" = masterQuery["#a#"] where "a" is the name of the query column.

After I had all of the query columns in the VARIABLES scope I tried to run a QofQ on the subQuery and low and behold I got an error message:

"coldfusion.sql.QueryColumn cannot be cast to coldfusion.sql.QueryTable"

However, when I tried a different method for bringing the masterQuery columns to the VARIABLES scope, I was able to get it to work: "#a#" = evaluate('masterQuery.#a#')

After doing some playing around using GetMetaData().getName() to determine what ColdFusion was seeing when a variable was set using masterQuery["#a#"] vs evaluate('masterQuery.#a#') I learned that ColdFusion continues to see the variable as a type: "QueryColumn" instead of its actual type (string, query, array, struct, etc...).

Currently the only way I can get CF to recognize the proper type is to use the evaluate() function. What's even more annoying is that you can't reference columns in the nested query so masterQuery.subQuery.TestColumn1[2] produces an error message.

I wonder if this a bug within ColdFusion 10. I haven't tested it in any earlier versions and after extensive searching I couldn't find anyone else with a similar problem. Regardless I thought it was pretty interesting and noteworthy.

If you want to see some sample code to generate this error for yourself, here it is:

http://pastebin.com/pBkjUtxc

<!--- start by building a new query object --->
<cfset masterQuery = QueryNew("ID,Name,SubQuery", "INTEGER,VARCHAR,OBJECT")>
 
<!--- add a row and some basic data --->
<cfset QueryAddRow(masterQuery)>
<cfset QuerySetCell(masterQuery, "ID",1)>
<cfset QuerySetCell(masterQuery, "Name", "Dave")>
   
<!--- create a new sub query --->
<cfset subQuery = QueryNew("TestColumn1,TestColumn2","VARCHAR,VARCHAR")>
 
<!--- lets add a few rows to the subQuery --->
<cfloop from="1" to="3" index="b">
       
        <!--- new row --->
        <cfset queryAddRow(subQuery)>
           <!--- subQuery data --->
        <cfset QuerySetCell(subQuery,"TestColumn1","Hello World")>
        <cfset QuerySetCell(subQuery,"TestColumn2","All Your Base are Belong to Us")>
       
</cfloop>
 
<!--- populate the master query column "subQuery" with the new query object we created --->
<cfset QuerySetCell(masterQuery, "SubQuery", subQuery)>
 
<!--- dump the data so we can make sure it looks good --->
<cfdump var="#masterQuery#">
 
 
<!--- now loop through the query, and assign all of the columns in the query to the variables scope --->
<cfloop list="#masterQuery.columnList#" index="a">
 
        <cfset "VARIABLES.#a#" = masterQuery["#a#"]>
 
</cfloop>
 
 
<!--- this generates an error in CF 10 (maybe other versions) --->
<cftry>
 
    <!--- try and run a QofQ --->
    <cfquery dbtype="query" name="getSubQuery">
    SELECT *
    FROM subQuery
    </cfquery>  
   
    <cfdump var="#getSubQuery#">
 
    <cfcatch type="any">
        <cfdump var="ERROR: #cfcatch.Message#">
    </cfcatch>
 
</cftry>
 
<!--- Now try using evaluate() instead.  This doesn't generate an error for some reason --->
<cfloop list="#masterQuery.columnList#" index="a">
 
        <cfset "VARIABLES.#a#" = evaluate('masterQuery.#a#')>
 
</cfloop>
 
<!--- run the QofQ again --->
<cfquery dbtype="query" name="getSubQuery">
SELECT *
FROM subQuery
</cfquery>
 
<cfdump var="#getSubQuery#">

Message was edited by: Henweigh99

Report · Feb 12, 2014

I think I may have solved my own issue... possibly.

After doing more research I stumbled upon another Ben Nadel article:
http://www.bennadel.com/blog/214-Using-The-ColdFusion-Query-Object-As-A-Complex-Object-Iterator.htm

In this article it mentions some complications when trying to reference complex objects within queries.

What I was missing in my pastebin code when trying to assign a variable to a column in the query was the bracket notation designating the desired row. I guess you have to do this when referring to complex object types.

So:

Should read:

Boom. Magic.

Hope this helps other people if they find themselves in a similar predicament.

Report · Feb 13, 2014

Henweigh99 wrote:
So:
<cfset "VARIABLES.#a#" = masterQuery["#a#"]>
Should read:
<cfset "VARIABLES.#a#" = masterQuery["#a#"][1]>

Could be better, much better! The following are just string assignments:

They contain a lot of complexity for the sake of complexity! A variable assignment would have been much better.

Let us therefore dissect the code. Consider the loop you reference:

</cfloop>

Assume that the columnList consists of "col1,col2,col3". Also assume that the query returns the following data:

col1 | col2 | col3

row 1: v11 | v12 | v13

row 2: v21 | v22 | v23

row 3: v31 | v32 | v33

row 4: v41 | v42 | v43

Then the loop will run 3 times, since columnList has 3 elements. The attribute index="a" implies that the variable a will successively take the values "col1", "col2", "col3" at each pass of the loop. The value of "VARIABLES.#a#" will therefore be, successively, the strings

"VARIABLES.col1"

"VARIABLES.col2"

"VARIABLES.col3"

Likewise, the variable evaluate('masterQuery.#a#') will take on the respective values

evaluate('masterQuery.col1')

evaluate('masterQuery.col2')

evaluate('masterQuery.col3')

These are, by default, the values of the first row of each column, hence:

v11

v12

v13

So, what your loop actually does is as follows:

First pass:

"VARIABLES.col1" = v11

Second pass:

"VARIABLES.col2" = v12

Third pass:

"VARIABLES.col3" = v13

You are essentially picking out the values of the first row! It would have been much simpler, and neater, to do something like this instead:

</cfloop>

Report · Feb 14, 2014

Thanks so much for the tip and the detailed breakdown BKBK! That's what I love about this forum, I always learn something new with each visit.

Adobe Community

Strange Behavior When Working with Nested Query Objects