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
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
Alex Treber 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, > - AlexMaybe use :conditions => [ " driver in (?)", list ] ? Stephan -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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... -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
> Maybe use > > :conditions => [ " driver in (?)", list ] ? > > StephanThanks 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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Alex Treber 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]) > endThis 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 Manges --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Alex Treber 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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Stephan Wehner 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 Manges --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---