Forum: Ruby on Rails Relationship navigation issue

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
B72610c28afc18f4b817770c8b9accd8?d=identicon&s=25 Neeraj Kumar (Guest)
on 2006-01-31 19:23
(Received via mailing list)
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.
F15fdc7cb2e911b3808837f2be244add?d=identicon&s=25 Adam Denenberg (Guest)
on 2006-01-31 23:03
(Received via mailing list)
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
4005a47a8f2ceee49670b920593c1d52?d=identicon&s=25 Ben Munat (Guest)
on 2006-02-01 03:19
(Received via mailing list)
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
This topic is locked and can not be replied to.