# Relationship navigation issue

**URL:** <https://www.ruby-forum.com/t/relationship-navigation-issue/53301>\
**Category:** Rails\
**Created:** [January 31, 2006, 7:23pm UTC](https://www.ruby-forum.com/t/relationship-navigation-issue/53301 "2006-01-31T19:23:24Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![nekapoor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ea666f/32.png) [@nekapoor](https://www.ruby-forum.com/u/nekapoor)\
**Post date:** [January 31, 2006, 7:23pm UTC](https://www.ruby-forum.com/t/relationship-navigation-issue/53301/1 "2006-01-31T19:23:24Z")

</div>

class Event \< ActiveRecord::Base  
belongs\_to :venue  
belongs\_to :category  
end

class Venue \< ActiveRecord::Base  
belongs\_to :city  
end

class City \< ActiveRecord::Base  
belongs\_to :state  
end

I want to retrieve all event records belonging to a state. Coming from  
hibernate background I tried something like this:

def self.list\_for\_a\_state(state\_id)  
find\_by\_sql([“select e.\* from venues v, events e where  
v.city.state\_id=? and  
e.venue\_id = v.id”,state\_id])  
end

Error: Unknown table ‘v.city’ in where clause

How do I handle this case?

Thanks.

---

<div class="post-metadata">

**Author:** ![nekapoor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ea666f/32.png) [@nekapoor](https://www.ruby-forum.com/u/nekapoor)\
**Post date:** [January 31, 2006, 11:03pm UTC](https://www.ruby-forum.com/t/relationship-navigation-issue/53301/2 "2006-01-31T23:03:17Z")

</div>

you need to use a join. I think i did see someone use the :through  
paramater on here but i dont see documentation of it anywhere.

select \* from events left join venues on events.venue\_id = venue.id  
left join cities on venue.city\_id = city.id left join state on  
city.state\_id = state.id where state.name = ‘NY’

adam

---

<div class="post-metadata">

**Author:** ![nekapoor](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/ea666f/32.png) [@nekapoor](https://www.ruby-forum.com/u/nekapoor)\
**Post date:** [February 1, 2006, 3:19am UTC](https://www.ruby-forum.com/t/relationship-navigation-issue/53301/3 "2006-02-01T03:19:38Z")

</div>

Yeah… I don’t think find\_by\_sql does anything other than param  
replacement (i.e. it’s  
not HQL). So, your dbms is looking for a table called “v.city”.

You can do the “join” syntax Adam metniond or something like [off the  
top of my head…]

select e.\* from venus v, events e, cities c  
where c.state\_id = ?  
and v.city\_id = c.id  
and e.venue\_id = v.id

In other words, find\_by\_sql is like a prepared statement, not hibernate.

You can also do it with rails find\_by methods and conditions, but I’ll  
leave that to  
someone more experienced to explain. The “:through” that Adam was  
referring to is  
something someone was just posting about in the last day or so… don’t  
think it’s in  
rails proper. (Unfortunately the gmane search ignores the “:” so it  
finds way too many  
false positives.)

Oh, and I think generally whenever you have belongs\_to on one end you  
want a has\_one or  
has\_many on the other end.

b