[Rails] Relationship navigation issue

[Ben Munat]

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



Adam Denenberg wrote:
> 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 1/31/06, Neeraj Kumar <neeraj.jsr at gmail.com> wrote:
> 
>>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.
>>
>>
>>_______________________________________________
>>Rails mailing list
>>Rails at lists.rubyonrails.org
>>http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>
>>
> 
> _______________________________________________
> Rails mailing list
> Rails at lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails