Forum: Ruby on Rails Query Question

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Alex T. (Guest)
on 2007-01-06 02:57
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
Stephan W. (Guest)
on 2007-01-06 03:01
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
Mitchell H. (Guest)
on 2007-01-06 10:43
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...
Alex T. (Guest)
on 2007-01-08 20:20
> 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
Dan M. (Guest)
on 2007-01-08 20:36
(Received via mailing list)
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.
Stephan W. (Guest)
on 2007-01-08 21:15
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
Dan M. (Guest)
on 2007-01-09 00:39
(Received via mailing list)
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.
Alex T. (Guest)
on 2007-01-09 16:06
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
This topic is locked and can not be replied to.