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