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:

      http://pastebin.com/pBkjUtxc

       

      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.

           

          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.

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

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

             

            </cfloop>

            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.