Relationship navigation issue


#1

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.


#2

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


#3

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