Query Question

Rails
1

Is it possible to use one result set in another query…

This is a kinda bogus example, but here goes

If a function returns a list of authors
@authorList = getAllAuthorsBetween(2000,2004)

then I wanted to get what car they are driving
@carList = getCarsDrivenBy(@authorList)

How can I write a query to do this?

def getCarsDrivenBy(list)
Car.find(:all, :conditions => [" driver = ?", list])
end

which in sql would be something vaguely like

select * from cars where driver in
(select Author from authors where date between 2000, 2004)

Cheers,

  • Alex
2

Alex T. wrote:

Is it possible to use one result set in another query…

This is a kinda bogus example, but here goes

If a function returns a list of authors
@authorList = getAllAuthorsBetween(2000,2004)

then I wanted to get what car they are driving
@carList = getCarsDrivenBy(@authorList)

How can I write a query to do this?

def getCarsDrivenBy(list)
Car.find(:all, :conditions => [" driver = ?", list])
end

which in sql would be something vaguely like

select * from cars where driver in
(select Author from authors where date between 2000, 2004)

Cheers,

  • Alex

Maybe use

:conditions => [ " driver in (?)", list ] ?

Stephan

3

If you’re talking about subqueries, as implmented in mySQL 4.1 (not sure
about other DBs), I don’t think rails has any support for it at all.
Maybe this is a call for a plugin…

4

Alex T. wrote:

How can I write a query to do this?

def getCarsDrivenBy(list)
Car.find(:all, :conditions => [" driver = ?", list])
end

This will do what you’re looking for:

@people = Person.find(:all)
def cars_driven_by(people)
Car.find(:all, :conditions => [“driver_id IN (?)”, people.map(&:id)])
end

people.map(&:id) will make an array of the IDs for the people.

Dan M.

5

Maybe use

:conditions => [ " driver in (?)", list ] ?

Stephan

Thanks for the idea, but its not working.

I broke the query slightly to get the resulting sql, and it was passing
NULL for list. SELECT * FROM cars WHERE (driver in (NULL))

While it would be useful to be able to use one result set in another
query you would need to be able to specify which column of the previous
results to use.

Trying list.name errors out with
undefined method `name’ for #Array:0x2b9e5584e480

I will probably end up just rewriting the query in the ‘in ()’ portion
SELECT * FROM cars WHERE (driver in (SELECT name FROM authors WHERE…))

It would still be cool to get it to work this way, but until I hear
otherwise I will assume its not possible

6

Stephan W. wrote:

I’m pretty sure that this feature was introduced for
Rails 1.2 although my syntax may be wrong.

This syntax is available in Rails 1.1.6 - it will work as I posted it.
You just need to collect the array of objects into an array of the IDs.

Dan M.

7

Dan, it worked great! Thanks for helping out, now to go and look back
and some other queries to see if I can use this anywhere else.

~ Alex

8

Alex T. wrote:

Maybe use

:conditions => [ " driver in (?)", list ] ?

Stephan

Thanks for the idea, but its not working.

I’m pretty sure that this feature was introduced for
Rails 1.2 although my syntax may be wrong.

Sorry, please look up yourself.

Stephan