Rails - Jan 2006 - Relationship navigation issue

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060131/f1aea7d9/attachment.html

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@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@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

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@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@lists.rubyonrails.org
>>http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>
>>
> 
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails