# Query Question

**URL:** <https://www.ruby-forum.com/t/query-question/81184>\
**Category:** Rails\
**Created:** [January 6, 2007, 1:57am UTC](https://www.ruby-forum.com/t/query-question/81184 "2007-01-06T01:57:46Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![canner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f9ae1b/32.png) [@canner](https://www.ruby-forum.com/u/canner)\
**Post date:** [January 6, 2007, 1:57am UTC](https://www.ruby-forum.com/t/query-question/81184/1 "2007-01-06T01:57:46Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![canner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f9ae1b/32.png) [@canner](https://www.ruby-forum.com/u/canner)\
**Post date:** [January 6, 2007, 2:01am UTC](https://www.ruby-forum.com/t/query-question/81184/2 "2007-01-06T02:01:49Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![canner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f9ae1b/32.png) [@canner](https://www.ruby-forum.com/u/canner)\
**Post date:** [January 6, 2007, 9:43am UTC](https://www.ruby-forum.com/t/query-question/81184/3 "2007-01-06T09:43:01Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![canner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f9ae1b/32.png) [@canner](https://www.ruby-forum.com/u/canner)\
**Post date:** [January 8, 2007, 7:36pm UTC](https://www.ruby-forum.com/t/query-question/81184/4 "2007-01-08T19:36:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![canner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f9ae1b/32.png) [@canner](https://www.ruby-forum.com/u/canner)\
**Post date:** [January 8, 2007, 7:20pm UTC](https://www.ruby-forum.com/t/query-question/81184/5 "2007-01-08T19:20:41Z")

</div>

> 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

---

<div class="post-metadata">

**Author:** ![canner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f9ae1b/32.png) [@canner](https://www.ruby-forum.com/u/canner)\
**Post date:** [January 8, 2007, 11:39pm UTC](https://www.ruby-forum.com/t/query-question/81184/6 "2007-01-08T23:39:58Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![canner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f9ae1b/32.png) [@canner](https://www.ruby-forum.com/u/canner)\
**Post date:** [January 9, 2007, 3:06pm UTC](https://www.ruby-forum.com/t/query-question/81184/7 "2007-01-09T15:06:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![canner](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/f9ae1b/32.png) [@canner](https://www.ruby-forum.com/u/canner)\
**Post date:** [January 8, 2007, 8:15pm UTC](https://www.ruby-forum.com/t/query-question/81184/8 "2007-01-08T20:15:02Z")

</div>

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