Using Inner/Outer join to eager fetch belongs_to associations

Rails
1

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.

2

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
3

On Nov 4, 7:00 am, Justin H. [email protected] wrote:

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

4

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

5

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
6

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. :wink:

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.