Hi Gang, Found your messages in my Junk Mail - was wondering why no-one responded!
Brian you're right on all fronts. The example I gave was a paging one, but it's only an example, the real issue is re-using *portions* of SQL code in other queries - and not the query result itself. Here is a way that I have re-used *portions* of SQL code in 1 CFC: ** Search() ** <cfquery> SELECT COUNT(OrderID) as TotalRows FROM #getFrom()# WHERE CustomerName like '%Keywords%' </cfquery> ** Count() ** <cfquery> SELECT #getSelect()# FROM #getFrom()# WHERE StatusID > 5 </cfquery> ** getSelect() ** DISTINCT OrderID, CustomerID, BillingAddressID, ShippingAddressID, OrderDate, OrderFile, OrderPrescriptionFile, OrderInvoicePrintedDate, OrderAgreementSigned, OrderShippingPrice, OrderDiscount, OrderComment, OrderProblem, OrderCancelled, StatusID, Status, StatusPhrased, CustomerID, CustomerName ** getFrom() ** Order INNER JOIN OrderStatus ON `Order`.OrderID = OrderStatus.OrderID INNER JOIN Status ON OrderStatus.StatusID = Status.StatusID INNER JOIN Customer ON `Order`.CustomerID = Customer.CustomerID The preceding methods all live in the same CFC. You will notice that the Search() method and Count() method share the same FROM clause by getting it from another method. But they have a different SELECT, WHERE, GROUP BY, and so forth, so the query results will be very different. Other methods may share the same WHERE but not the same SELECT and even more methods may SHARE different parts. So you see, it's not a matter of caching queries, but building queries using encapsulated SQL. I am finding that my gateways CFCs (those that return queries) are generally using the same SQL for each method in the CFC. And that makes sense because if you want a COUNT it usually is for a record-set you want I the future. Same for SUM, GROUP, etc... These are all different representations of the same base. So SQL being a form of code like any other, should be re-used and encapsulated like any other. It doesn't seem proper to have to find and paste the same changes in 15-20 methods just because a little logic in the SQL changes. Thoughts? Baz -----Original Message----- From: Brian Peddle [mailto:[EMAIL PROTECTED] Sent: Friday, February 03, 2006 9:07 AM To: CF-Talk Subject: RE: SQL Encapsulation? I was basing my assumptions on writing these same things over and over from job to job for past 10 years. I assumed with the top 50 he is paging. I am also assuming he built up his where clause using a series of 'if' or 'case' statements so it won't always be the same. So if someone has 50 dropdowns for a report generator the person could pick no options or pick all 50. But it seems as though he has disappeared from our discussion so your are right, its us guessing what he want. -----Original Message----- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Friday, February 03, 2006 8:08 AM To: CF-Talk Subject: Re: SQL Encapsulation? 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:231245 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=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
