Well, we were both interpreting your request a bit incorrectly then. But, I'm not sure I have an answer for you on this one. It's not something I'd do. I'd be more apt to make a view that "encapsulated" the generic joined data, and then pull the columns (selects) under the conditions (wheres) that I wanted. I find the method you're using below to be difficult to read, and while I understand the concept, it's one of those places where I fail to see the benefit of doing it that way.
On 2/3/06, Baz <[EMAIL PROTECTED]> wrote: > > 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:231252 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
