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]<[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#%").setMode("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/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
