# Find records based on associated table's colums **URL:** \ **Category:** Rails\ **Created:** [April 4, 2006, 4:06pm UTC](https://www.ruby-forum.com/t/find-records-based-on-associated-tables-colums/56820 "2006-04-04T16:06:22Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![ingoweiss](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/77aa72/32.png) [@ingoweiss](https://www.ruby-forum.com/u/ingoweiss)\ **Post date:** [April 4, 2006, 4:06pm UTC](https://www.ruby-forum.com/t/find-records-based-on-associated-tables-colums/56820/1 "2006-04-04T16:06:22Z")
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 ---
**Author:** ![ingoweiss](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/77aa72/32.png) [@ingoweiss](https://www.ruby-forum.com/u/ingoweiss)\ **Post date:** [April 4, 2006, 4:36pm UTC](https://www.ruby-forum.com/t/find-records-based-on-associated-tables-colums/56820/2 "2006-04-04T16:36:52Z")
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 ---
**Author:** ![ingoweiss](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/77aa72/32.png) [@ingoweiss](https://www.ruby-forum.com/u/ingoweiss)\ **Post date:** [April 4, 2006, 6:10pm UTC](https://www.ruby-forum.com/t/find-records-based-on-associated-tables-colums/56820/3 "2006-04-04T18:10:51Z")
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 ---
**Author:** ![ingoweiss](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/77aa72/32.png) [@ingoweiss](https://www.ruby-forum.com/u/ingoweiss)\ **Post date:** [April 4, 2006, 6:28pm UTC](https://www.ruby-forum.com/t/find-records-based-on-associated-tables-colums/56820/4 "2006-04-04T18:28:11Z")
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