Find records based on associated table's colums

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

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

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

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 forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs