Find records based on associated table's colums

Rails
1

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

2

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

3

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

4

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