# Querying on join-tables in Rails

**URL:** <https://www.ruby-forum.com/t/querying-on-join-tables-in-rails/83524>\
**Category:** Rails\
**Created:** [January 26, 2007, 10:30pm UTC](https://www.ruby-forum.com/t/querying-on-join-tables-in-rails/83524 "2007-01-26T22:30:13Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![P-daddy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/db5fbb/32.png) [@P-daddy](https://www.ruby-forum.com/u/P-daddy)\
**Post date:** [January 26, 2007, 10:30pm UTC](https://www.ruby-forum.com/t/querying-on-join-tables-in-rails/83524/1 "2007-01-26T22:30:13Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![P-daddy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/db5fbb/32.png) [@P-daddy](https://www.ruby-forum.com/u/P-daddy)\
**Post date:** [January 26, 2007, 11:34pm UTC](https://www.ruby-forum.com/t/querying-on-join-tables-in-rails/83524/2 "2007-01-26T23:34:22Z")

</div>

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:

---

<div class="post-metadata">

**Author:** ![P-daddy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/db5fbb/32.png) [@P-daddy](https://www.ruby-forum.com/u/P-daddy)\
**Post date:** [January 27, 2007, 2:25am UTC](https://www.ruby-forum.com/t/querying-on-join-tables-in-rails/83524/3 "2007-01-27T02:25:07Z")

</div>

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