Forum: Ruby on Rails Find records based on associated table's colums

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.
Ingo W. (Guest)
on 2006-04-04 18:06
Hello,

Let's say I have a model for a "house". Each house has_a "city", which
in turn has_a "state" which in turn has_a "country". The objective is to
retrieve all houses in a given state, say "Massachusetts".

Being new to Ruby on Rails what I like is that things that should be
simple usually are simple, and since we have easy access to associated
tables via properties, I would expect to be able to write something like
this:

House.find(:all, conditions => [house.city.state.name ==
"Massachusetts"])

Instead, it seems like I need to use SQL in the condition, and I have no
idea how to solve the above problem with SQL. How can this be done? Or
am I missing something? I hope I am!

Ingo
Ingo W. (Guest)
on 2006-04-04 18:36
I found a "solution" that, well, works. I would be horrified if this
would be the simplest way to solve the problem....


@country = Country.find_by_name("USA")

@state = Region.find(  :first,
                       :conditions => ["name_short = ? and country_id =
?", "MA", @country.id])

@cities = City.find( :all,
                     :conditions => ["state_id = ?", @state.id])


@city_ids = @cities.collect{|city| city.id }.join("|")

@houses = House.find( :all,
                      :conditions => ["city_id = ?", @city_ids])


















Ingo W. wrote:
> Hello,
>
> Let's say I have a model for a "house". Each house has_a "city", which
> in turn has_a "state" which in turn has_a "country". The objective is to
> retrieve all houses in a given state, say "Massachusetts".
>
> Being new to Ruby on Rails what I like is that things that should be
> simple usually are simple, and since we have easy access to associated
> tables via properties, I would expect to be able to write something like
> this:
>
> House.find(:all, conditions => [house.city.state.name ==
> "Massachusetts"])
>
> Instead, it seems like I need to use SQL in the condition, and I have no
> idea how to solve the above problem with SQL. How can this be done? Or
> am I missing something? I hope I am!
>
> Ingo
Steve K. (Guest)
on 2006-04-04 20:10
In the State model do you have

has_many :cities

And in City model do you have

belongs_to :state
has_many: houses

And in House do you have

belongs_to :city

Ingo W. wrote:
> Hello,
>
> Let's say I have a model for a "house". Each house has_a "city", which
> in turn has_a "state" which in turn has_a "country". The objective is to
> retrieve all houses in a given state, say "Massachusetts".
>
> Being new to Ruby on Rails what I like is that things that should be
> simple usually are simple, and since we have easy access to associated
> tables via properties, I would expect to be able to write something like
> this:
>
> House.find(:all, conditions => [house.city.state.name ==
> "Massachusetts"])
>
> Instead, it seems like I need to use SQL in the condition, and I have no
> idea how to solve the above problem with SQL. How can this be done? Or
> am I missing something? I hope I am!
>
> Ingo
Ingo W. (Guest)
on 2006-04-04 20:28
(Received via mailing list)
Yes, that is the way I specified the associations

Ingo


>
> belongs_to :city










> Ingo W. wrote:
> > Hello,
> >
> > Let's say I have a model for a "house". Each house has_a "city",
which
> > in turn has_a "state" which in turn has_a "country". The objective
is
> to
> > retrieve all houses in a given state, say "Massachusetts".
> >
> > Being new to Ruby on Rails what I like is that things that should be
> > simple usually are simple, and since we have easy access to
associated
> > tables via properties, I would expect to be able to write something
> like
> > this:
> >
> > House.find(:all, conditions => [house.city.state.name ==
> > "Massachusetts"])
> >
> > Instead, it seems like I need to use SQL in the condition, and I
have
> no
> > idea how to solve the above problem with SQL. How can this be done?
Or
This topic is locked and can not be replied to.