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] 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/ -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
