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