3 Replies Latest reply on Feb 14, 2014 7:07 PM by Homestar9

    Strange Behavior When Working with Nested Query Objects

    Homestar9 Level 1

      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:



      Message was edited by: Henweigh99

        • 1. Re: Strange Behavior When Working with Nested Query Objects
          Homestar9 Level 1

          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-Ite rator.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.



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

          • 2. Re: Strange Behavior When Working with Nested Query Objects
            BKBK Adobe Community Professional & MVP

            Henweigh99 wrote:



            <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#')>




            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






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






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






            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[a] = masterQuery[a][1]>



            1 person found this helpful
            • 3. Re: Strange Behavior When Working with Nested Query Objects
              Homestar9 Level 1

              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.