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.
on 2008-11-04 07:45
on 2008-11-04 08:01
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 2008-11-04 10:32
On Nov 4, 7:00 am, Justin Holzer <jshol...@gmail.com> 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
on 2008-11-04 17:56
But is a plugin really required? Can't you feed something to find_by_sql that will get you both types of objects?
on 2008-11-05 05:48
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 2008-11-05 20:24
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.