Querying on join-tables in Rails

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

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 K. 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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs