15 Replies Latest reply: Mar 12, 2012 6:47 AM by Adam Cameron. RSS

    CFC function multiple queries returns

    TheScarecrow Community Member

      I have a cfc and I am wanting to have a function return multiple queries?  Is that possible? Here is what I have .. and I cant seem to get it to work. Thanks.

       

      <cffunction name="classvideo" access="public" returntype="query">

        <cfset var classvideo="">

        <cfif not IsDefined("URL.VideoID")>

          <cflocation url="class.cfm?videoID=486">

        <cfelse>

          <cfquery name="classvideo" datasource="dsn">

                SELECT video_path, ID, Video_Name

          FROM Video

          WHERE ID = <cfqueryparam value="#URL.VideoID#" cfsqltype="cf_sql_integer">

                </cfquery>

        </cfif>

       

        <cfquery name="VideoList" datasource="dsn">

                SELECT Video_Name, Video_Length, ID, category, School_Year

          FROM Video

          WHERE category = 'class'AND School_Year = '11-12'

      </cfquery><!------>

        <cfreturn classvideo>

       

      </cffunction>

        • 1. Re: CFC function multiple queries returns
          Owain North Community Member

          Yup, there are three ways I can initially think of.

           

          1) Set returntype="array", and return an array of queries

          better...

          2) Set returntype="struct", and return a struct with two properties which are the querysets, which you can name

          even better...

          3) Create a class which has two properties which are the query objects, you can then pass it around as a "strongly-typed" object. For example

           

          Create a cfc:

           

          <cfcomponent name="MyMethodReturnObject">

           

            <cfproperty name="Prop1" type="Query" />

            <cfproperty name="Prop2" type="Query" />

           

            <cffunction name="init" access="public" returntype="MyMethodReturnObject" >

              <cfargument name="P1" type="query" required="true" />

              <cfargument name="P2" type="query" required="true" />

              <cfset Prop1 = arguments.P1 />

              <cfset Prop2 = arguments.P2 />

            </cffunction>

           

          Then in your calling CFC, run the two queries, then do this:

           

          <cfreturn new MyMethodReturnObject(q1, q2) />

           

          Job done, pow pow pow.

           

          </cfcomponent>

          • 2. Re: CFC function multiple queries returns
            TheScarecrow Community Member

            So something like this? how do I access them on the page that is calling it?

             

            <cffunction name="classvideo" access="public" returntype="query">

                 <cfset var videoStruct=StructNew()>

              <cfif not IsDefined("URL.VideoID")>

                <cflocation url="class.cfm?videoID=486">

              <cfelse>

                <cfquery name="classvideo" datasource="dsn">

                      SELECT video_path, ID, Video_Name

                FROM Video

                WHERE ID = <cfqueryparam value="#URL.VideoID#" cfsqltype="cf_sql_integer">

                      </cfquery>

              </cfif>

             

              <cfquery name="VideoList" datasource="dsn">

                      SELECT Video_Name, Video_Length, ID, category, School_Year

                FROM Video

                WHERE category = 'class'AND School_Year = '11-12'

            </cfquery><!------>

             

            <cfset videoStruct.aged240video = aged240video>

              <cfset videoStruct.VideoList= VideoList>

              <cfreturn videoStruct>

             

             

            </cffunction>

            • 3. Re: CFC function multiple queries returns
              -==cfSearching==- Community Member

              Not quite.

               

              <cffunction name="classvideo" access="public" returntype="query">

              The function is returning a structure, not a query.

               

               

                <cfif not IsDefined("URL.VideoID")>

              The function should define VideoID as a required argument, then use #arguments.VideoID# instead of #URL.VideoID#.

               

                      ie   <cffunction ....>

                               <cfargument name="VideoID" .....>

               

              <cfquery name="classvideo" datasource="dsn">

              Do not forget to VAR scope all of the variables, including the two query names.

               

              how do I access them on the page that is calling it?

               

              The function returns a structure containing two keys:  "VideoList" and "aged240video". Just use the key names, like with any structure

               

                           #yourResultStructure.VideoList#

                           #yourResultStructure.aged240video#

               

               

              I am wanting to have a function return multiple queries?

               

               

              Just curious .. do you really need to return both queries from a single function call?

               

              Message was edited by: -==cfSearching==-

              • 4. Re: CFC function multiple queries returns
                TheScarecrow Community Member

                ok The page has video playing on it that it gets from the url... then it builds a list of other videos that can be played in that group.  I just figured it would be easier with two queries.  I guess it could be done with one.

                • 5. Re: CFC function multiple queries returns
                  -==cfSearching==- Community Member

                  Well actually I was asking if you really needed to return both results from one function, rather than just calling function1 then function2. But yes, you could probably write a more generic function to query the "Video" table based on what arguments were passed in.

                  • 6. Re: CFC function multiple queries returns
                    BKBK MVP

                    TheScarecrow wrote:

                     

                    I have a cfc and I am wanting to have a function return multiple queries?  Is that possible? Here is what I have .. and I cant seem to get it to work. Thanks.

                     

                    <cffunction name="classvideo" access="public" returntype="query">

                      <cfset var classvideo="">

                      <cfif not IsDefined("URL.VideoID")>

                        <cflocation url="class.cfm?videoID=486">

                      <cfelse>

                        <cfquery name="classvideo" datasource="dsn">

                              SELECT video_path, ID, Video_Name

                        FROM Video

                        WHERE ID = <cfqueryparam value="#URL.VideoID#" cfsqltype="cf_sql_integer">

                              </cfquery>

                      </cfif>

                     

                      <cfquery name="VideoList" datasource="dsn">

                              SELECT Video_Name, Video_Length, ID, category, School_Year

                        FROM Video

                        WHERE category = 'class'AND School_Year = '11-12'

                    </cfquery><!------>

                      <cfreturn classvideo>

                    </cffunction>

                    You posted very similar code in a previous thread. You haven't used the most valuable suggestion people gave you there, namely, passing the video ID as an argument. The result is that you get people to waste their time here repeating advice already given to you elsewhere.

                     

                    Think about your CFC yourself. (For a start, you must use the <cfcomponent> tag). Its context is different from that of a CFM page. So what happens when the CFM page opens, bringing in a videoID via the URL?

                     

                    The page knows URL.videoID, but the CFC does not. So when you instantiate the CFC and call the function, URL.videoID will be undefined and the function will redirect you to class.cfm?videoID=486.

                     

                    Presumably, on the page class.cfm you will attempt to instantiate the CFC and call the function again. If this is indeed so, you will end up in an infinite loop. This would have manifested itself as server failure.

                     

                    As colleagues have said, pass videoID as an argument. You know it makes sense. Good luck.

                    • 7. Re: CFC function multiple queries returns
                      BKBK MVP

                      Owain North wrote:

                       

                      Yup, there are three ways I can initially think of.

                       

                      1) Set returntype="array", and return an array of queries

                      better...

                      2) Set returntype="struct", and return a struct with two properties which are the querysets, which you can name

                      even better...

                      3) Create a class which has two properties which are the query objects, you can then pass it around as a "strongly-typed" object. For example

                       

                      Quite comprehensive. My personal choice is struct. I find it simpler. I would also help garbage-collection by using var, and make a deep copy of the queries to avoid any references to copies in the function.

                       

                      <cfcomponent>

                      <cffunction name="classvideo" access="public" returntype="struct">

                      <cfargument name="videoID" type="numeric" required="yes">

                       

                      <cfset var video=structNew()>

                      <cfset var classvideo="">

                        <cfset var videoList="">

                       

                          <cfquery name="classvideo" datasource="dsn">

                              SELECT video_path, ID, Video_Name,

                              FROM Video

                              WHERE ID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.videoID#">

                          </cfquery>

                       

                      <cfquery name="videoList" datasource="dsn">

                               SELECT Video_Name, Video_Length, ID, category, School_Year

                               FROM Video

                               WHERE category = 'class' AND School_Year = '11-12'

                      </cfquery>

                       

                      <cfset video.classvideo = duplicate(classvideo)>

                      <cfset video.videoList= duplicate(videoList)>

                       

                        <cfreturn video>

                       

                      </cffunction>

                      </cfcomponent>

                      • 8. Re: CFC function multiple queries returns
                        Adam Cameron. Community Member

                        Quite comprehensive. My personal choice is struct. I find it simpler. I would also help garbage-collection by using var, and make a deep copy of the queries to avoid any references to copies in the function.

                         

                        [...]

                         

                        <cfset video.classvideo = duplicate(classvideo)>

                        <cfset video.videoList= duplicate(videoList)>

                         

                          <cfreturn video>

                         

                         

                        Provided the "video" variable is VARed (which it should be), or the CFC instance is transient, there's no need to do this.  The reference in the function will be destroyed at the return statement, meaning the only reference left will be the one in the calling code anyhow.  This is just adding (a trivial) performance overhead and code-clutter, whilst achieving nothing beneficial.

                         

                        --

                        Adam

                        • 9. Re: CFC function multiple queries returns
                          BKBK MVP

                          Adam Cameron. wrote:

                           

                          Quite comprehensive. My personal choice is struct. I find it simpler. I would also help garbage-collection by using var, and make a deep copy of the queries to avoid any references to copies in the function.

                           

                          [...]

                           

                          <cfset video.classvideo = duplicate(classvideo)>

                          <cfset video.videoList= duplicate(videoList)>

                           

                            <cfreturn video>

                           

                           

                          Provided the "video" variable is VARed (which it should be), or the CFC instance is transient, there's no need to do this.  The reference in the function will be destroyed at the return statement, meaning the only reference left will be the one in the calling code anyhow.  This is just adding (a trivial) performance overhead and code-clutter, whilst achieving nothing beneficial.

                          I can see your overall point. There is no arguing really with what you say.

                           

                          However, I think you miss one subtle point, that the garbage collector has a life of its own, beyond your control. The variables classvideo, videoList and video have been VARed. So let's follow your reasoning and write instead:

                           

                          <cfset video.classvideo = classvideo>

                          <cfset video.videoList= videoList>

                           

                          What happens if the garbage collector should decide to collect video before it does classvideo or videoList?

                          • 10. Re: CFC function multiple queries returns
                            Adam Cameron. Community Member

                            However, I think you miss one subtle point, that the garbage collector has a life of its own, beyond your control. The variables classvideo, videoList and video have been VARed. So let's follow your reasoning and write instead:

                             

                            <cfset video.classvideo = classvideo>

                            <cfset video.videoList= videoList>

                             

                            What happens if the garbage collector should decide to collect video before it does classvideo or videoList?

                             

                            It won't.  GC doesn't work on references, it works on the underlying objects in memory.  Whilst there's a reference to the object, it won't get GCed (unless it's specifically created as - for example - a soft reference (which it is not)).

                             

                            classvideo and video.classvideo are two references to the same object, so the object won't get GCed until both those references are deleted.

                             

                            --

                            Adam

                            • 11. Re: CFC function multiple queries returns
                              BKBK MVP

                              Adam Cameron. wrote:

                               

                              However, I think you miss one subtle point, that the garbage collector has a life of its own, beyond your control. The variables classvideo, videoList and video have been VARed. So let's follow your reasoning and write instead:

                               

                              <cfset video.classvideo = classvideo>

                              <cfset video.videoList= videoList>

                               

                              What happens if the garbage collector should decide to collect video before it does classvideo or videoList?

                               

                              It won't.  GC doesn't work on references, it works on the underlying objects in memory.  Whilst there's a reference to the object, it won't get GCed (unless it's specifically created as - for example - a soft reference (which it is not)).

                               

                              I at first didn't understand why you would say "It won't" and mention references.  It made sense when I reread my piece.

                               

                              The garbage collector may decide to check a variable for collection (it's been marked Var after all), though it might not actually collect it in the end.

                               

                              classvideo and video.classvideo are two references to the same object, so the object won't get GCed until both those references are deleted.

                              My point exactly! If you had used duplicate() you would have had 2 references to 2 separate objects.

                              • 12. Re: CFC function multiple queries returns
                                Owain North Community Member

                                Uh-oh, here we go again...

                                • 13. Re: CFC function multiple queries returns
                                  BKBK MVP

                                  Owain, your lightning speed denied me the chance to be able to update my last post. I wished to add this to the first part:

                                   

                                  My bad. I could have been much clearer.

                                  • 14. Re: CFC function multiple queries returns
                                    Owain North Community Member

                                    So we're all still friends, right?

                                    • 15. Re: CFC function multiple queries returns
                                      Adam Cameron. Community Member

                                      Uh-oh, here we go again...

                                       

                                      Chuckle.

                                       

                                      No, trust me... we won't be going anywhere.

                                       

                                      --

                                      Adam