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: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
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:231241 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
RE: SQL Encapsulation?
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
Re: SQL Encapsulation?
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
RE: SQL Encapsulation?
I'm not such a big fan of that method either - that's why I wrote the post! :) Maybe I should just make better use of views... In any case, I appreciate the time you spent giving it thought. Cheers, Baz -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: Friday, February 03, 2006 11:25 AM To: CF-Talk Subject: Re: SQL Encapsulation? 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
Re: SQL Encapsulation?
Wouldn't this be easier in a Stored Procedure? Well, SQL Encapsulation I mean... This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Baz [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Fri Feb 03 15:14:27 2006 Subject: RE: SQL Encapsulation? 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
RE: SQL Encapsulation?
I just rewrote a query as a test a few weeks ago that replaced what you have below. The search would query for a count first then grab the 50/100 etc records to be displayed for paging. When someone queried the whole database it was taking upwards of 50 seconds to run both queries. I moved them into a stored proc. Queried the information and inserting it into a temp table with an auto id. Then I would query records 1-50, 51-100, 101-150 etc. depending where on the page the user was. I also am able to do a quick count of records on that temp table to get total records which is much after than essentially running same query twice. The total time dropped to 13-22 seconds for the query. I haven't tested under heavy load to see how the temp table stuff will hold up but it could be one path to follow for you. Maybe others have some thoughts on the temp table method. -Original Message- From: Baz [mailto:[EMAIL PROTECTED] Sent: Thursday, February 02, 2006 9:25 AM To: CF-Talk Subject: SQL Encapsulation? Like any other code, there are times when portions of your SQL queries need to be re-used or duplicated. A common example is having a method that returns a COUNT of a query based on some conditions, and having another method that returns some rows from that query. For instance: *** getCount() *** SELECT Count(OrderID) as OrderIDCount FROM OrderTable INNER JOIN StatusTable ON (OrderTable.OrderID= StatusTable.OrderID) WHERE (whatever) *** getRows() *** SELECT TOP 50 OrderID, StatusID FROM OrderTable INNER JOIN StatusTable ON (OrderTable.OrderID= StatusTable.OrderID) WHERE (whatever) In the preceding code, the FROM clause is repeated in both methods, as is the WHERE clause. Now in a real-world complex app, that FROM would actually be 10-15 lines long, and the WHERE could have hundreds of lines of complex logic based on passed in arguments. Plus you may need not 2, but 5, 10, 20 methods using the same FROM and/or WHERE and/or SELECT. So my question is how do people handle this? If you had 15 methods with complex WHERE and FROM clauses that are repeated intermittently, and then something changes, it's an error-prone hassle to copy paste all the affected sections. Thoughts? Baz ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231139 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
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:231164 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
RE: SQL Encapsulation?
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:231173 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
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:231179 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
RE: SQL Encapsulation?
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:231191 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
