• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

CFC function multiple queries returns

Community Beginner ,
Mar 09, 2012 Mar 09, 2012

Copy link to clipboard

Copied

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>

Views

6.4K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 09, 2012 Mar 09, 2012

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 09, 2012 Mar 09, 2012

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 09, 2012 Mar 09, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Beginner ,
Mar 09, 2012 Mar 09, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 09, 2012 Mar 09, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 12, 2012 Mar 12, 2012

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 12, 2012 Mar 12, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 12, 2012 Mar 12, 2012

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 12, 2012 Mar 12, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 12, 2012 Mar 12, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 12, 2012 Mar 12, 2012

Copy link to clipboard

Copied

Uh-oh, here we go again...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 12, 2012 Mar 12, 2012

Copy link to clipboard

Copied

LATEST

Uh-oh, here we go again...

Chuckle.

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

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 12, 2012 Mar 12, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guide ,
Mar 12, 2012 Mar 12, 2012

Copy link to clipboard

Copied

So we're all still friends, right?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 11, 2012 Mar 11, 2012

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation