Skip to main content
Homestar9
Homestar9
Inspiring
February 13, 2014
Question

Strange Behavior When Working with Nested Query Objects

  • February 13, 2014
  • 1 reply
  • 1131 views

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 

  1. <!--- start by building a new query object --->
  2. <cfset masterQuery = QueryNew("ID,Name,SubQuery", "INTEGER,VARCHAR,OBJECT")>
  3.  
  4. <!--- add a row and some basic data --->
  5. <cfset QueryAddRow(masterQuery)>
  6. <cfset QuerySetCell(masterQuery, "ID",1)>
  7. <cfset QuerySetCell(masterQuery, "Name", "Dave")>
  8.    
  9. <!--- create a new sub query --->
  10. <cfset subQuery = QueryNew("TestColumn1,TestColumn2","VARCHAR,VARCHAR")>
  11.  
  12. <!--- lets add a few rows to the subQuery --->
  13. <cfloop from="1" to="3" index="b">
  14.        
  15.         <!--- new row --->
  16.         <cfset queryAddRow(subQuery)>
  17.        
    1.     <!--- subQuery data --->
  18.         <cfset QuerySetCell(subQuery,"TestColumn1","Hello World")>
  19.         <cfset QuerySetCell(subQuery,"TestColumn2","All Your Base are Belong to Us")>
  20.        
  21. </cfloop>
  22.  
  23. <!--- populate the master query column "subQuery" with the new query object we created --->
  24. <cfset QuerySetCell(masterQuery, "SubQuery", subQuery)>
  25.  
  26. <!--- dump the data so we can make sure it looks good --->
  27. <cfdump var="#masterQuery#">
  28.  
  29.  
  30. <!--- now loop through the query, and assign all of the columns in the query to the variables scope --->
  31. <cfloop list="#masterQuery.columnList#" index="a">
  32.  
  33.         <cfset "VARIABLES.#a#" = masterQuery["#a#"]>
  34.  
  35. </cfloop>
  36.  
  37.  
  38. <!--- this generates an error in CF 10 (maybe other versions) --->
  39. <cftry>
  40.  
  41.     <!--- try and run a QofQ --->
  42.     <cfquery dbtype="query" name="getSubQuery">
  43.     SELECT *
  44.     FROM subQuery
  45.     </cfquery>  
  46.    
  47.     <cfdump var="#getSubQuery#">
  48.  
  49.     <cfcatch type="any">
  50.         <cfdump var="ERROR: #cfcatch.Message#">
  51.     </cfcatch>
  52.  
  53. </cftry>
  54.  
  55. <!--- Now try using evaluate() instead.  This doesn't generate an error for some reason --->
  56. <cfloop list="#masterQuery.columnList#" index="a">
  57.  
  58.         <cfset "VARIABLES.#a#" = evaluate('masterQuery.#a#')>
  59.  
  60. </cfloop>
  61.  
  62. <!--- run the QofQ again --->
  63. <cfquery dbtype="query" name="getSubQuery">
  64. SELECT *
  65. FROM subQuery
  66. </cfquery>
  67.  
  68. <cfdump var="#getSubQuery#">

Message was edited by: Henweigh99

This topic has been closed for replies.

1 reply

Homestar9
Homestar9Author
Inspiring
February 13, 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:

<cfset "VARIABLES.#a#" = masterQuery["#a#"]>

Should read:

<cfset "VARIABLES.#a#" = masterQuery["#a#"][1]>

Boom. Magic.

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

BKBK
Community Expert
BKBKCommunity Expert
Community Expert
February 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:

<cfset "VARIABLES.#a#" = masterQuery["#a#"][1]>

<cfset "VARIABLES.#a#" = evaluate('masterQuery.#a#')>

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 list="#masterQuery.columnList#" index="a">

        <cfset "VARIABLES.#a#" = evaluate('masterQuery.#a#')>

</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:

<cfset firstRowData = structNew()>

<cfloop list="#masterQuery.columnList#" index="a">

        <cfset firstRowData = masterQuery[1]>

</cfloop>

Homestar9
Homestar9Author
Inspiring
February 15, 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.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices