Well, first of all, I didn't read his initial question to be one of pagination, which is what you're implying. I read it as one of reusing query results in multiple ways. In that scenario, you could very easily cache your object, either in the application or the session scope. So, you'd be storing more in memory, but only making a single trip to the database. It's all trade-offs. Obviously, if you're working with millions of rows of data, this might not be the best approach. But, it seemed reasonable to me that if he's indicating that the where clause could have "hundreds of lines of complex logic based on passed in arguments" that he'd ultimately be returning a relatively small result set.
Honestly, I'd be doing my best not to design an app that has hundreds of lines of complex logic for a single query, as I'd find that seriously unmaintainable. But, without knowing more specifics about what he's actually trying to acomplish, we're just tossing out potential solution scenarios. We don't really know the paramaters of the problem we're trying to solve. On 2/2/06, Brian Peddle <[EMAIL PROTECTED]> wrote: > > Using this method you would be bringing back potentially hundreds of > thousands of records. > > He only needs 50 at a time. Why pass 100,000 or more maybe when you can > send 50 records back to ColdFusion. > > -----Original Message----- > From: Deanna Schneider [mailto:[EMAIL PROTECTED] > Sent: Thursday, February 02, 2006 2:39 PM > To: CF-Talk > Subject: Re: SQL Encapsulation? > > pseudo-code... > <cfcomponent displayname="mystuff" hint="Get's all my stuff"> > > <cfset variables.allrows = ""> > > > <cffunction access="public" name="init" returntype="mystuff" > output="false" hint="Initializes the object."> > <!--- Do big complex query here and set it to the > variables.allrows var ---> > <cfquery....> > Select blah blah > From Blah blah > WHERE blah blah > </cfquery> > > <cfset variables.allrows = myquery> > <cfreturn this /> > </cffunction> > > <cffunction access="public" name="getTotal" output="false" > returntype="numeric"> > <cfreturn variables.allrows.recordcount> > </cffunction> > > <cffunction access="public" name="getNext" output="false" > returntype="query"> > <cfargument name="start" required="false" default="1" type="numeric"> > <cfargument name="end" required="false" default="50" type="numeric"> > <cfset var getit = ""> > <cfquery name="getit" dbtype="query"> > select * > from variables.allrows > WHERE start = #arguments.start# > AND end = #arguments.end# > </cfquery> > > <cfreturn getit> > </cffunction> > </cfcomponent> > > > On 2/2/06, Brian Peddle <[EMAIL PROTECTED]> wrote: > > > > I don't believe that will get him what he wants. > > > > He has query #1 which will get the total records for a query. > > Query #2 just returns 50 records of the total. > > > > So when you display results you see. > > > > Total: Records 4530 Displaying Records: 101-150. > > > > Instead of one query returning all 4530 records just to display 50 he is > > just bringing back what he needs. > > > > > > > > -----Original Message----- > > From: Deanna Schneider [mailto:[EMAIL PROTECTED] > > Sent: Thursday, February 02, 2006 1:43 PM > > To: CF-Talk > > Subject: Re: SQL Encapsulation? > > > > I'd go the cfc route, where I would instantiate a single CFC that would > > run > > your base query with all your where and from clauses. Then, I'd have > > multiple functions that would do query of query against that base query. > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231238 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
