Skip navigation
TheScarecrow
Currently Being Moderated

CFC function multiple queries returns

Mar 9, 2012 10:31 AM

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>

 
Replies
  • Currently Being Moderated
    Mar 9, 2012 10:36 AM   in reply to TheScarecrow

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 9, 2012 12:33 PM   in reply to TheScarecrow

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 9, 2012 1:03 PM   in reply to TheScarecrow

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 11, 2012 12:08 AM   in reply to TheScarecrow

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 1:41 AM   in reply to Owain North

    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>

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 2:05 AM   in reply to BKBK

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 4:32 AM   in reply to Adam Cameron.

    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?

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 4:50 AM   in reply to BKBK

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 6:34 AM   in reply to Adam Cameron.

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 6:35 AM   in reply to BKBK

    Uh-oh, here we go again...

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 6:38 AM   in reply to BKBK

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 6:39 AM   in reply to BKBK

    So we're all still friends, right?

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 12, 2012 6:47 AM   in reply to Owain North

    Uh-oh, here we go again...

     

    Chuckle.

     

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

     

    --

    Adam

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points