Readability is an important consideration, however any SQL ties you to a specific persistence model. What if you wish to persist the data as xml tomorrow, or as a big text file or as bunches of bananas that have nothing to do with SQL. You will now have to go rewrite all of your sql into appropriate code to persist the data in the new model. By using OO queries you would have to rewrite nothing, as long as there existed the plug in for that type of persistence. At worst you'd write the plug in, and all of your code would continue to work no matter what persistence mechanism it was ported to. You are creating failure points and bugs by writing SQL where you could be writing OO code, if there is any chance it will not exist on the same dbms. If that is not something you will ever have to worry about I'd skip the OO queries, and maybe reactor as well and just write the database specific sql.
The OO query language is similar enough to the sql it should not be that difficult to read, and if it is it should be improved to make it more readable rather than ignoring the benefits. ________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brian Kotek Sent: Wednesday, January 10, 2007 2:37 PM To: [email protected] Subject: Re: [Reactor for CF] GetWhere with Two OR's Unless you're using database specific functions this won't matter. And even if you are, I'd assert that it is easier for someone to tweak an Oracle query to get it to work on SQL Server than it would be for someone unfamiliar with Reactor to try and figure out what a 50 line OO query is doing and how to change it. Personal opinion of course but that's my take. On 1/9/07, Porter, Benjamin L. <[EMAIL PROTECTED]> wrote: The only down side to that is now you lose the database agnostic portion of your code and create unnecessary dependencies on a specific dbms. In reality almost all web apps never change the database they are using. ________________________________ From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> ] On Behalf Of Brian Kotek Sent: Tuesday, January 09, 2007 12:33 AM To: [email protected] Subject: Re: [Reactor for CF] GetWhere with Two OR's Once you start getting into queries like this, you really have to ask yourself if you should be trying to do this with the OO queries. I'd give some thought to just placing the SQL into a custom gateway method. Just my 2 cents. Brian On 1/2/07, Jon Clausen <[EMAIL PROTECTED]> wrote: Benjamin, Thanks so much for your help - and your feedback. The actual query I had written using reactor does have explicit fields in the select, so I probably should have included those. I'll have to try using a sub-query, though. Very good points! Regards, Jon On Jan 2, 2007, at 8:37 AM, Porter, Benjamin L. wrote: First off explicitly request each field you want do not rely on points.* use points.x, points.y, etc. The way you have written the query is very expensive and potentially slow. By using the or in the where clause on both the point userid and the userpoint connect for the userid you force the dbms to create a huge set of values to search through. There is no way for the dbms to trim the results ahead of time. You would be better off writing the query with sub queries that return just the identity column of one of the tables that only has the ids that could be valid for that table. As it is written you are forcing scans of a derived table of points.recordcount * userpt_connect.recordcount rows of data. But here is the oo query code for that query. <cfscript> <cfset qry = CreateQuery()/> <cfset subWhere = qry.getWhere().createWhere() /> <cfscript> qry.returnObjectField("Points", "id"); // join to user points connect qry.leftJoin("points"," userpt_connect "," userpt_connect "); // search for search var in point description or point_name qry.getWhere().isLike("points","point_description",searchVar); qry.getWhere().setMode("or"); qry.getWhere().isLike("points","point_name",searchVar); // the join to the sub where clause is an and join qry.getWhere().setMode("and"); // create a sub where clause or the user id subWhere.setMode("or"); subWhere.isEqual("point","point_userid",UserID); subWhere.isEqual("userpt_connect","userpt_connect_userid",userID); // join the two wheres together qry.getWhere().addWhere(subWhere); Results = GetByQuery(qry); </cfscript> ________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jon Clausen Sent: Sunday, December 31, 2006 3:12 PM To: [email protected] Subject: [Reactor for CF] GetWhere with Two OR's I have the following SQL which I have been trying to turn into the equivalent a Reactor Query: SELECT points.* FROM points AS points LEFT JOIN userpt_connect AS userpt_connect ON points.point_id = userpt_connect.userpt_connect_point WHERE (points.point_description LIKE '%#searchVar#%' OR points.point_name LIKE '%#searchVar#%') AND (points.point_userid = #userID# OR userpt_connect.userpt_connect_userid = #userID#) I've tried the following for the getWhere() methods: gQuery.getWhere().isLike("points","point_description","%#searchVar#%").s etMode("or").isLike("points","point_name","%#searchVar#%").setMode("and" ).isEqual("points","point_userid",userID).setMode("or").isEqual("userpt_ connect","userpt_connect_userid",userID); and even using getWhereCommands(): gQuery.getWhere().getWhereCommands("(point_description LIKE '%#searchVar#%' OR point_name LIKE '%#searchVar#%') AND ( points.point_userid = #userID# OR userpt_connect.userpt_connect_userid = #userID#)"); - which outputs no SQL in the generated query, so I must be using that incorrectly. What I can't seem to get is how to qualify the two separate "OR" statements separately. Any help would be appreciated. Jon ************************************************************************ **** This email may contain confidential material. If you were not an intended recipient, Please notify the sender and delete all copies. We may monitor email to and from our network. ************************************************************************ *** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- ************************************************************************ **** This email may contain confidential material. If you were not an intended recipient, Please notify the sender and delete all copies. We may monitor email to and from our network. ************************************************************************ *** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- **************************************************************************** This email may contain confidential material. If you were not an intended recipient, Please notify the sender and delete all copies. We may monitor email to and from our network. **************************************************************************** -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Reactor for ColdFusion Mailing List [email protected] Archives at: http://www.mail-archive.com/reactor%40doughughes.net/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
