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