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.
on 2006-01-31 20:23
on 2006-02-01 00:03
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
on 2006-02-01 04:19
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