Rails - Mar 2006 - Left Joins with Rails?

Everyone,

I have two tables (assuming their most logical types):

apartments
 id
 address
 bedrooms
 bathrooms
 googlemaps_id

googlemaps
 id
 url


the models:

	class Apartment < ActiveRecord::Base
		has_one :googlemap
	end

	class Googlemap < ActiveRecord::Base
		belongs_to :listing
	end

I would like to find_all by searching the attributes in apartment, and
also return the google map associated with that apartment, and a null
(or empty string) if there''s no google map in its table associated
with the apartment''s id.  In sql I would do this with a left join, and
while I could used the find_by_sql method I was wondering if there''s a
more practical "Rails" way of doing this.

In the apartments controller I have something for found results like

def results
	@apartments = Listing.find(:all, :conditions => ["bedrooms=? AND
bathrooms=?", :params])
end

But that will only return the columns in Apartments.  How would I do
that to get the googlemaps url if there is a corresponding value?

Thanks all!

- Joel

> the models:
>
> 	class Apartment < ActiveRecord::Base
> 		has_one :googlemap
> 	end
>
> 	class Googlemap < ActiveRecord::Base
> 		belongs_to :listing
> 	end
>
> I would like to find_all by searching the attributes in apartment, and
> also return the google map associated with that apartment, and a null
> (or empty string) if there''s no google map in its table associated
> with the apartment''s id.  In sql I would do this with a left join,
and
> while I could used the find_by_sql method I was wondering if
there''s a
> more practical "Rails" way of doing this.
>
> In the apartments controller I have something for found results like
>
> def results
> 	@apartments = Listing.find(:all, :conditions => ["bedrooms=? AND
> bathrooms=?", :params])
> end
>
> But that will only return the columns in Apartments.  How would I do
> that to get the googlemaps url if there is a corresponding value?
First of all, your model name is Apartment, not Listing, so the code  
you have above should only return an error.  But that aside...

When you specified the has_one and belongs_to in your models, you  
told Rails how to set up the left join.  This means you don''t need to  
do it yourself.  Any Apartment object will have a class method called  
googlemap.  This is how you get the corresponding googlemap.

@apartment = Apartment.find(:first)
@apartment.googlemap

Now, if you know for sure that you are going to use the googlemap,  
and you don''t want rails to use two sql statements (which it would  
using the above code), then you can use the :include symbol to  
specify what we call "eager loading".  Eager loading just means
"go
ahead and do the join cuz I know I''m gonna need it".

@apartment = Apartment.find(:first, :include => :googlemap)
@apartment.googlemap

-Derrick Spell

Easy, but you''re confusing me a bit with your models... You have n
apartment table, but you reference Listing in the googlemap model and in
the controller. Also, you''re a little backwards there on your joins.


If you did this:

	class Apartment < ActiveRecord::Base
		belongs_to :googlemap # because apartments HAS THE
FOREIGN KEY COLUMN
	end

	class Googlemap < ActiveRecord::Base
		has_one :listing
	end
	
Then you can do this:

def results
	@apartments = Apartment.find(:all,
		   :include =>[:googlemap]
               :conditions => ["bedrooms=? AND bathrooms=?",
:params])
End


Or do it like you do now.
 When you reference @apartment.googlemap.url, your data will be found.
However, if you use the above method, then the data will be fetched all
at once, as opposed to only upon request.

See "eager loading of associations" in the manual.

*** Also, I don''t understand your data model, but I would probably
avoid
the whole mess and put the field on the apartments table... Unless
googlemaps table will have more columns than just the map URL.


-bph


-----Original Message-----
From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Joel Oliveira
Sent: Thursday, March 02, 2006 12:51 PM
To: rails@lists.rubyonrails.org
Subject: [Rails] Left Joins with Rails?


Everyone,

I have two tables (assuming their most logical types):

apartments
 id
 address
 bedrooms
 bathrooms
 googlemaps_id

googlemaps
 id
 url


the models:

	class Apartment < ActiveRecord::Base
		has_one :googlemap
	end

	class Googlemap < ActiveRecord::Base
		belongs_to :listing
	end

I would like to find_all by searching the attributes in apartment, and
also return the google map associated with that apartment, and a null
(or empty string) if there''s no google map in its table associated with
the apartment''s id.  In sql I would do this with a left join, and while
I could used the find_by_sql method I was wondering if there''s a more
practical "Rails" way of doing this.

In the apartments controller I have something for found results like

def results
	@apartments = Listing.find(:all, :conditions => ["bedrooms=? AND
bathrooms=?", :params]) end

But that will only return the columns in Apartments.  How would I do
that to get the googlemaps url if there is a corresponding value?

Thanks all!

- Joel
_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Boy am I an idiot!

Thanks guys - I finally see where I was going wrong.

- Joel

On 3/2/06, Hogan, Brian P. <HOGANBP@uwec.edu>
wrote:
> Easy, but you''re confusing me a bit with your models... You have n
> apartment table, but you reference Listing in the googlemap model and in
> the controller. Also, you''re a little backwards there on your
joins.
>
>
> If you did this:
>
>         class Apartment < ActiveRecord::Base
>                 belongs_to :googlemap # because apartments HAS THE
> FOREIGN KEY COLUMN
>         end
>
>         class Googlemap < ActiveRecord::Base
>                 has_one :listing
>         end
>
> Then you can do this:
>
> def results
>         @apartments = Apartment.find(:all,
>                    :include =>[:googlemap]
>                :conditions => ["bedrooms=? AND bathrooms=?",
:params])
> End
>
>
> Or do it like you do now.
>  When you reference @apartment.googlemap.url, your data will be found.
> However, if you use the above method, then the data will be fetched all
> at once, as opposed to only upon request.
>
> See "eager loading of associations" in the manual.
>
> *** Also, I don''t understand your data model, but I would probably
avoid
> the whole mess and put the field on the apartments table... Unless
> googlemaps table will have more columns than just the map URL.
>
>
> -bph
>
>
> -----Original Message-----
> From: rails-bounces@lists.rubyonrails.org
> [mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Joel Oliveira
> Sent: Thursday, March 02, 2006 12:51 PM
> To: rails@lists.rubyonrails.org
> Subject: [Rails] Left Joins with Rails?
>
>
> Everyone,
>
> I have two tables (assuming their most logical types):
>
> apartments
>  id
>  address
>  bedrooms
>  bathrooms
>  googlemaps_id
>
> googlemaps
>  id
>  url
>
>
> the models:
>
>         class Apartment < ActiveRecord::Base
>                 has_one :googlemap
>         end
>
>         class Googlemap < ActiveRecord::Base
>                 belongs_to :listing
>         end
>
> I would like to find_all by searching the attributes in apartment, and
> also return the google map associated with that apartment, and a null
> (or empty string) if there''s no google map in its table associated
with
> the apartment''s id.  In sql I would do this with a left join, and
while
> I could used the find_by_sql method I was wondering if there''s a
more
> practical "Rails" way of doing this.
>
> In the apartments controller I have something for found results like
>
> def results
>         @apartments = Listing.find(:all, :conditions =>
["bedrooms=? AND
> bathrooms=?", :params]) end
>
> But that will only return the columns in Apartments.  How would I do
> that to get the googlemaps url if there is a corresponding value?
>
> Thanks all!
>
> - Joel
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>