Rails - Nov 2008 - Using Inner/Outer join to eager fetch belongs_to associations

I was wondering if it was possible to have rails use an inner/outer
join to eager fetch a belongs_to association, rather than having it
generate multiple SQL queries?

In this case, here are my model objects with the assoications:

class Restaurant < ActiveRecord::Base
  belongs_to :state
end

class State < ActiveRecord::Base
  has_many :restaurants
end


In my controller action, I am currently doing...
  @restaurant = Restaurant.find(params[:id], :include => [:state])

... which is causing multiple SQL queries to be generated, one to
select the restaurant info, and the other to select the state info. Is
there a way to easily override this default behavior and have rails
generate a single query with an INNER or OUTER join while still
populating the state info for the restaurant?

I did a bit of research on the changes to ActiveRecord between 2.0 and
2.1, and I understand why the default behavior is to generate multiple
SQL queries, since in the case where you have a relationship such as
has_many that is referenced in the :include, if ActiveRecord were to
use a single SQL query, AR might end up having to parse a number of
records much larger than the number of distinct rows you will be
displaying.

I am also aware that if you invoke a condition on a row not in the
table corresponding to the model object in the call to find(), that
ActiveRecord will fall back on the old method of generating a single
query with joins. However, I would prefer to not add any conditions,
as that seems like kind of a hack. If absolutely necessary, I suppose
I a condition like "states.id = restaurants.state_id" could be added,
but that just seems altogether ugly.

However, in some cases, such as the one I mention above, I think it
might be desirable to be able to ask ActiveRecord to generate a single
query, since the result of the SQL would be at most a single row
anyhow.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

I did a bit more looking in to using a :conditions hash to force
ActiveRecord to fall back on the Rails 2.0 method of eager fetching
associations, and this is absolutely not what I am looking for. For
one, the Rails 2.0 eager fetch strategy always uses OUTER JOINs, and
it also includes a join for all associations specified as an :include,
rather than just those that are specified in a condition.

I am looking for some way to essentially force ActiveRecord to eagerly
fetch specific associations/:include''s using an INNER JOIN in the
query that is also being used to fetch the data for the base object.
The more I think about it, the more I think that this functionality
does not really exist in ActiveRecord, and it would have to be written
as an extension/plugin.

However, since I''m pretty new to Rails, I wanted to put it to all the
experts on this forum before throwing in the towel.

- Justin

On Nov 4, 1:45 am, Justin Holzer
<jshol...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I was wondering if it was possible to have rails use an inner/outer
> join to eager fetch a belongs_to association, rather than having it
> generate multiple SQL queries?
>
> In this case, here are my model objects with the assoications:
>
> class Restaurant < ActiveRecord::Base
>   belongs_to :state
> end
>
> class State < ActiveRecord::Base
>   has_many :restaurants
> end
>
> In my controller action, I am currently doing...
>   @restaurant = Restaurant.find(params[:id], :include => [:state])
>
> ... which is causing multiple SQL queries to be generated, one to
> select the restaurant info, and the other to select the state info. Is
> there a way to easily override this default behavior and have rails
> generate a single query with an INNER or OUTER join while still
> populating the state info for the restaurant?
>
> I did a bit of research on the changes to ActiveRecord between 2.0 and
> 2.1, and I understand why the default behavior is to generate multiple
> SQL queries, since in the case where you have a relationship such as
> has_many that is referenced in the :include, if ActiveRecord were to
> use a single SQL query, AR might end up having to parse a number of
> records much larger than the number of distinct rows you will be
> displaying.
>
> I am also aware that if you invoke a condition on a row not in the
> table corresponding to the model object in the call to find(), that
> ActiveRecord will fall back on the old method of generating a single
> query with joins. However, I would prefer to not add any conditions,
> as that seems like kind of a hack. If absolutely necessary, I suppose
> I a condition like "states.id = restaurants.state_id" could be
added,
> but that just seems altogether ugly.
>
> However, in some cases, such as the one I mention above, I think it
> might be desirable to be able to ask ActiveRecord to generate a single
> query, since the result of the SQL would be at most a single row
> anyhow.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Nov 4, 7:00 am, Justin Holzer
<jshol...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I did a bit more looking in to using a :conditions hash to force
> ActiveRecord to fall back on the Rails 2.0 method of eager fetching
> associations, and this is absolutely not what I am looking for. For
> one, the Rails 2.0 eager fetch strategy always uses OUTER JOINs, and
> it also includes a join for all associations specified as an :include,
> rather than just those that are specified in a condition.
>
> I am looking for some way to essentially force ActiveRecord to eagerly
> fetch specific associations/:include''s using an INNER JOIN in the
> query that is also being used to fetch the data for the base object.
> The more I think about it, the more I think that this functionality
> does not really exist in ActiveRecord, and it would have to be written
> as an extension/plugin.
>
> However, since I''m pretty new to Rails, I wanted to put it to all
the
> experts on this forum before throwing in the towel.
Yup you''ve got it pretty much all figured out. For what it''s
worth,
when it was just a belongs_to/has_one, the overhead of generating the
crazy query with the joins, the database running the query, rails
doing its fancy parse thingy on the results meant that

Foo.find 123456, :include => :bar

was often no faster than
f = Foo.find 123456
Bar.find 456798

Fred
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

But is a plugin really required?  Can''t you feed something to
find_by_sql that will get you both types of objects?

-----Original Message-----
From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Frederick Cheung
Sent: Tuesday, November 04, 2008 1:32 AM
To: Ruby on Rails: Talk
Subject: [Rails] Re: Using Inner/Outer join to eager fetch belongs_to
associations




On Nov 4, 7:00 am, Justin Holzer
<jshol...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I did a bit more looking in to using a :conditions hash to force
> ActiveRecord to fall back on the Rails 2.0 method of eager fetching
> associations, and this is absolutely not what I am looking for. For
> one, the Rails 2.0 eager fetch strategy always uses OUTER JOINs, and
> it also includes a join for all associations specified as an :include,
> rather than just those that are specified in a condition.
>
> I am looking for some way to essentially force ActiveRecord to eagerly
> fetch specific associations/:include''s using an INNER JOIN in the
> query that is also being used to fetch the data for the base object.
> The more I think about it, the more I think that this functionality
> does not really exist in ActiveRecord, and it would have to be written
> as an extension/plugin.
>
> However, since I''m pretty new to Rails, I wanted to put it to all
the
> experts on this forum before throwing in the towel.
Yup you''ve got it pretty much all figured out. For what it''s
worth, when it was just a belongs_to/has_one, the overhead of generating the
crazy query with the joins, the database running the query, rails doing its
fancy parse thingy on the results meant that

Foo.find 123456, :include => :bar

was often no faster than
f = Foo.find 123456
Bar.find 456798

Fred


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

I''m sure you could write a SQL query to fetch everything with a single
query. However, one of the things I like about ORM, be it
ActiveRecord, Hibernate, etc, is not writing SQL, and being able to do
write the data access code in the native language (Ruby, Java, C#,
etc.). Of course, it is comforting to know that you can always fall
back on SQL when you need it, but most of the time, I would prefer to
let the ORM generate the SQL.

I was simply curious to see if it was possible to get ActiveRecord to
fetch data for the base object plus some of it''s associations in a
single query using INNER JOINs. It appears this is not possible
without either resorting to SQL queries (find_by_sql), or creating
some kind of extension for ActiveRecord, in the case where you don''t
want to use SQL. If it ever becomes a major issue for me, I guess I''ll
just have to pick one of the two methods, unless of course, by that
time someone has already written a plugin.

Thanks for the responses.

- Justin

On Nov 4, 11:55 am, "Pardee, Roy"
<parde...-go57ItdSaco@public.gmane.org> wrote:
> But is a plugin really required?  Can''t you feed something to
find_by_sql that will get you both types of objects?
>
> -----Original Message-----
> From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Frederick Cheung
> Sent: Tuesday, November 04, 2008 1:32 AM
> To: Ruby on Rails: Talk
> Subject: [Rails] Re: Using Inner/Outer join to eager fetch belongs_to
associations
>
> On Nov 4, 7:00 am,
JustinHolzer<jshol...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > I did a bit more looking in to using a :conditions hash to force
> > ActiveRecord to fall back on the Rails 2.0 method of eager fetching
> > associations, and this is absolutely not what I am looking for. For
> > one, the Rails 2.0 eager fetch strategy always uses OUTER JOINs, and
> > it also includes a join for all associations specified as an :include,
> > rather than just those that are specified in a condition.
>
> > I am looking for some way to essentially force ActiveRecord to eagerly
> > fetch specific associations/:include''s using an INNER JOIN in
the
> > query that is also being used to fetch the data for the base object.
> > The more I think about it, the more I think that this functionality
> > does not really exist in ActiveRecord, and it would have to be written
> > as an extension/plugin.
>
> > However, since I''m pretty new to Rails, I wanted to put it to
all the
> > experts on this forum before throwing in the towel.
>
> Yup you''ve got it pretty much all figured out. For what
it''s worth, when it was just a belongs_to/has_one, the overhead of
generating the crazy query with the joins, the database running the query, rails
doing its fancy parse thingy on the results meant that
>
> Foo.find 123456, :include => :bar
>
> was often no faster than
> f = Foo.find 123456
> Bar.find 456798
>
> Fred
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Fetch, for sure--I''m just unsure whether AR will use the extra data to
actually populate the child objects.  If only I wasn''t so lazy,
I''d consult the docs and/or try it out. ;-)

Definitely take your point about taking advantage of the ORM.  But it seems to
me that some people get fetishistic about avoiding SQL--are willing to torture
AR into generating the exact SQL they want.  That''s fun for a bit, but
there''s definitely a point where AR leaves you hanging--which is why
find_by_sql exists.

-----Original Message-----
From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
[mailto:rubyonrails-talk@googlegroups.com] On Behalf Of Justin Holzer
Sent: Tuesday, November 04, 2008 8:48 PM
To: Ruby on Rails: Talk
Subject: [Rails] Re: Using Inner/Outer join to eager fetch belongs_to
associations


I''m sure you could write a SQL query to fetch everything with a single
query. However, one of the things I like about ORM, be it ActiveRecord,
Hibernate, etc, is not writing SQL, and being able to do write the data access
code in the native language (Ruby, Java, C#, etc.). Of course, it is comforting
to know that you can always fall back on SQL when you need it, but most of the
time, I would prefer to let the ORM generate the SQL.

I was simply curious to see if it was possible to get ActiveRecord to fetch data
for the base object plus some of it''s associations in a single query
using INNER JOINs. It appears this is not possible without either resorting to
SQL queries (find_by_sql), or creating some kind of extension for ActiveRecord,
in the case where you don''t want to use SQL. If it ever becomes a major
issue for me, I guess I''ll just have to pick one of the two methods,
unless of course, by that time someone has already written a plugin.

Thanks for the responses.

- Justin

On Nov 4, 11:55 am, "Pardee, Roy"
<parde...-go57ItdSaco@public.gmane.org> wrote:
> But is a plugin really required?  Can''t you feed something to
find_by_sql that will get you both types of objects?
>
> -----Original Message-----
> From: rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
> [mailto:rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org] On Behalf
Of Frederick
> Cheung
> Sent: Tuesday, November 04, 2008 1:32 AM
> To: Ruby on Rails: Talk
> Subject: [Rails] Re: Using Inner/Outer join to eager fetch belongs_to
> associations
>
> On Nov 4, 7:00 am,
JustinHolzer<jshol...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > I did a bit more looking in to using a :conditions hash to force
> > ActiveRecord to fall back on the Rails 2.0 method of eager fetching
> > associations, and this is absolutely not what I am looking for. For
> > one, the Rails 2.0 eager fetch strategy always uses OUTER JOINs, and
> > it also includes a join for all associations specified as an
> > :include, rather than just those that are specified in a condition.
>
> > I am looking for some way to essentially force ActiveRecord to
> > eagerly fetch specific associations/:include''s using an INNER
JOIN
> > in the query that is also being used to fetch the data for the base
object.
> > The more I think about it, the more I think that this functionality
> > does not really exist in ActiveRecord, and it would have to be
> > written as an extension/plugin.
>
> > However, since I''m pretty new to Rails, I wanted to put it to
all
> > the experts on this forum before throwing in the towel.
>
> Yup you''ve got it pretty much all figured out. For what
it''s worth,
> when it was just a belongs_to/has_one, the overhead of generating the
> crazy query with the joins, the database running the query, rails
> doing its fancy parse thingy on the results meant that
>
> Foo.find 123456, :include => :bar
>
> was often no faster than
> f = Foo.find 123456
> Bar.find 456798
>
> Fred

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---