Hi, I''m using rails 1.2 & mysql 5
I have 2 database tables, Cars and Passengers
Cars table has the following columns
id: primary key
car_name: varchar(100)
Passengers table has the following columns
id: primary key
first_name: varchar(50)
last_name: varchar(50)
car_id: foreign_key referencing Cars id
Now let''s say the Cars table has 2 rows:
1,Herbie The Love Bug
2,Lightning McQueen
Let''s say the Passengers table has 4 rows:
1,Don,Adams,1
2,Anette,Funicello,1
3,Rodrigo,Faria,2
4,Esmerelda,Villalobos,2
****************************
Now, I want to use rails to retrieve
all CARS and ALL PASSENGERS where a car contains someone whose last name
is "Villalobos"
****************************************
this is pretty straightforward in MySQL, requiring the "where in"
clause
and a subquery.
e.g.
select * from Cars
left join (Passengers) on (Cars.id = Passengers.car_id)
where
Cars.id in (select Car_id from Passengers where last_name
''Villalobos'');
Now, my moronic guesses as to how to pull this off within the Rails
framework haven''t worked and I can''t seem to find anything
addressing
this issue specifically in this forum, the wiki or on google.
**********************
I''m trying to avoid using SQL directly within rails and I''m
trying to
figure out if there''s a way to reference the Passengers table via the
Cars has_many Passengers attribute
**************************
I tried adding this method within the relevant controller class:
def my_method
@cars = Car.find(:all, :conditions => ["last_name = ?",
"Villalobos"])
end
Specifically, I get the following error:
Mysql::Error: Unknown column ''Passengers.last_name'' in
''where clause'':
SELECT Cars.id, Cars.car_name FROM Cars WHERE (Passengers.last_name
''Villalobos'')
I then tried to be more explicit by supplying the joined table
@cars = Car.find(:all, :conditions => ["Passengers.last_name =
?",
"Villalobos"], :include => ["Passengers"])
but this gives me no results and no syntax error.
Is there a simple way (via Rails) to say,
Give me an array of Car objects that have passengers whose last_name is
"Villalobos"?
TIA
--
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 I was doing this I might do something like... @cars = Car.find(:all, :conditions => ["Cars.id in (select Car_id from Passengers where last_name = ?)","Villalobos"], :joins=> "left join (Passengers) on (Cars.id = Passengers.car_id)") It doesn''t seem very Rail-errific, but I''ll bet somebody else''s money it would work. HTH, Andy P-daddy schrieb:> Hi, I''m using rails 1.2 & mysql 5 > > I have 2 database tables, Cars and Passengers > > Cars table has the following columns > id: primary key > car_name: varchar(100) > > Passengers table has the following columns > id: primary key > first_name: varchar(50) > last_name: varchar(50) > car_id: foreign_key referencing Cars id > > > Now let''s say the Cars table has 2 rows: > > 1,Herbie The Love Bug > 2,Lightning McQueen > > Let''s say the Passengers table has 4 rows: > > 1,Don,Adams,1 > 2,Anette,Funicello,1 > 3,Rodrigo,Faria,2 > 4,Esmerelda,Villalobos,2 > > **************************** > Now, I want to use rails to retrieve > all CARS and ALL PASSENGERS where a car contains someone whose last name > is "Villalobos" > **************************************** > > this is pretty straightforward in MySQL, requiring the "where in" clause > and a subquery. > > e.g. > > select * from Cars > left join (Passengers) on (Cars.id = Passengers.car_id) > where > Cars.id in (select Car_id from Passengers where last_name > ''Villalobos''); > > > Now, my moronic guesses as to how to pull this off within the Rails > framework haven''t worked and I can''t seem to find anything addressing > this issue specifically in this forum, the wiki or on google. > > ********************** > I''m trying to avoid using SQL directly within rails and I''m trying to > figure out if there''s a way to reference the Passengers table via the > Cars has_many Passengers attribute > ************************** > > I tried adding this method within the relevant controller class: > def my_method > @cars = Car.find(:all, :conditions => ["last_name = ?", "Villalobos"]) > end > Specifically, I get the following error: > Mysql::Error: Unknown column ''Passengers.last_name'' in ''where clause'': > SELECT Cars.id, Cars.car_name FROM Cars WHERE (Passengers.last_name > ''Villalobos'') > > I then tried to be more explicit by supplying the joined table > @cars = Car.find(:all, :conditions => ["Passengers.last_name = ?", > "Villalobos"], :include => ["Passengers"]) > but this gives me no results and no syntax error. > > Is there a simple way (via Rails) to say, > > Give me an array of Car objects that have passengers whose last_name is > "Villalobos"? > > TIA >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Andy Koch wrote:> If I was doing this I might do something like... > > @cars = Car.find(:all, > :conditions => ["Cars.id in (select Car_id from Passengers where > last_name = ?)","Villalobos"], > :joins=> "left join (Passengers) on (Cars.id = Passengers.car_id)") > > > It doesn''t seem very Rail-errific, but I''ll bet somebody else''s money it > would work. > > HTH, > > Andy > > P-daddy schrieb:Andy, thank you. unfortunately, that''s the kind of thing i wanted to avoid (sql-heavy syntax). but thank you anyway. P-d -- 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 -~----------~----~----~----~------~----~------~--~---