Rails - Apr 2006 - 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


-- 
Posted via http://www.ruby-forum.com/.

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 Weiss 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

-- 
Posted via http://www.ruby-forum.com/.

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 Weiss 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

-- 
Posted via http://www.ruby-forum.com/.

Yes, that is the way I specified the associations

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 Weiss 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
> 
>

If you use the new has_many :through functionality, you could do:

    @state = State.find(params[:id]) # Where params[:id] is the id of
Massachusets

The you can get all the houses simply by accessing @state.houses. For
that to work your State model must look like this:

    class State < ActiveRecord::Base
        has_many :cities
        has_many :houses, :through => :cities
    end

On 4/4/06, Ingo Weiss <ingo01@ingoweiss.com>
wrote:
> Yes, that is the way I specified the associations
>
> 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 Weiss 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
> >
> >
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>