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#
%").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/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
