RE: [Reactor for CF] sub queries

Gareth Cole Tue, 20 Jun 2006 08:54:40 -0700

Hi Teddy,

Thanks for the suggestion. I’ll give it a go and see if it works.

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Teddy Payne
Sent: 20 June 2006 16:39
To: [email protected]
Subject: Re: [Reactor for CF] sub queries

Doug,
Couldn't this be simply re-written as:

select
    *
from
    jobs j
    join jobLocation l
        k.jobID = l.jobID
where
    j.category = x
    and
    j.type = y
    and
    l.locationID = z

And use the linked table notation in the <objects /> to use the join query notation to get what Gareth is looking for?

Teddy

On 6/20/06, Doug Hughes <[EMAIL PROTECTED]> wrote:

This sounds to me like a case where it would be most appropriate to write a custom query.

Doug

From: [EMAIL PROTECTED] [mailto: [EMAIL PROTECTED]] On Behalf Of Gareth Cole
Sent: Tuesday, June 20, 2006 11:10 AM
To: [email protected]
Subject: [Reactor for CF] sub queries

Hi Doug,

My apologies if this has already been covered, but I couldn't see anything about this in the list archive.

I'm building a search on a jobs table, in which the user can specify a number of optional criteria

e.g.

SELECT *

FROM jobs

WHERE category=X

AND type=Y

……

Doing this in reactor, I've found the getByFields(), or using an OOP query in the gateway to work very well.

My problem, is that each job can belong to several locations – this is modelled in the DB using a 'location' table and linking via 'jobLocation' table.

Normally I would use a subquery to allow the user to narrow search results to a particular location e.g.

SELECT *

FROM jobs

WHERE category=X

AND type=Y

AND jobID IN (SELECT jobID FROM jobLocation WHERE locationID=Z)

Is this possible with reactor?

If not, it's no problem as I can very easily build a custom method into my job gateway

Thanks

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

--
<cf_payne />
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Reactor for ColdFusion Mailing List
[email protected]
Archives at: http://www.mail-archive.com/reactor%40doughughes.net/
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

RE: [Reactor for CF] sub queries

Reply via email to