ActiveRecord :select with :include
ActiveRecord is an important slice of the joy included in Ruby on Rails, writing a SQL database driven application without actually writing any SQL is really pleasurable.
But sometime, even in the joy you get some pain.
The pain for me arrived when I was working on some reporting feature that was pulling various data across a quite long chain of has_many relationships.
I experienced a quite high load in the DB at the moment of the query and after that seconds of CPU full load eaten by my ruby process.
I checked what was going on in my log/development.log and I noticed the query was extracting all the columns of every table even if I specified a :select option, this turned a query originally quite light into a heavy one.
I googled a bit and I found out that with ActiveRecord, if you specify :select and :include option you don't get anymore 100% joy but just 50%, your :select statement will be ignored.
Today I'm trying to put back some percent of the joy.
select_with_include gem is meant to move the joy level up to 80% (no, not 100% yet)
select_with_include will allow you to specify a limited :select statement like
"table1.column1, table1.column3, table2.column2"
or like
"table1.*, table2.column3, table2.column4, talbe3.*"
At the moment you can't specify functions or calculated fields. There are reason for that and I'm going to discuss these reason in the next posts. I'll try as well to explain how this gem works around the original ActiveRecord code.
For the moment you're invited to get select_with_include gem using
sudo gem install select_with_include
and test it yourself specifying
require 'select_with_include'
in your config/environment.rb file.
If you're a script/console user you can just try, while you
tail -f log/development.log
to launch the same find with :include and :select with and without requiring the gem.
If you find any issue you're really welcome to use the issue tracker
And if you want to know more just stay tuned for the next posts
7 comments:
This is a very useful gem - had no problems with it so far. If functions could be integrated too, it would be near perfect! Thanks a lot.
Thanks for the fix.
I can't help but feel frustrated about how long this bug has been around and ignored.
If there is one bug in rails that drives me nuts, or I run into on a consistent basis, it's this one.
Thanks for this - it's a big help. But, i did run into one bug using a has_many :through association. With such an association, say book.authors, the find method is broken (book.authors.find) as the call to construct_select is passing 2 arguments instead of 1. I patched this as follows:
Index: has_many_through_association.rb
===================================================================
--- has_many_through_association.rb (revision 230)
+++ has_many_through_association.rb (working copy)
@@ -16,7 +16,7 @@
options[:order] = @reflection.options[:order]
end
- options[:select] = construct_select(options[:select], options[:include])
+ options[:select] = construct_select(options[:select])
options[:from] ||= construct_from
options[:joins] = construct_joins(options[:joins])
options[:include] = @reflection.source_reflection.options[:include] if options[:include].nil?
I'm not sure this is entirely correct, as i haven't examined the code carefully. But, it seems that construct_select is not doing much, and doesn't need the options[:include] at all.
thanks again
rich
This is a fantastic gem - I've been using it in several Rails apps over the last few months and it has been entirely transparent, no problems at all, and it is very useful indeed.
Is there any chance of incorporating SQL functions? I'll have a look at the code myself if I get a chance; I would imagine it wouldn't take too much to implement?
@rob_w if you look on rails trac bug 7041 there are already patches that implement SQL functions and aliases.
It doesn't look like your using the google code svn for revision control?
Too bad, it would have been nice to do:
./script/plugin install http://ar-select-with-include.googlecode.com/svn/tags/ar-select-with-include
@engtech yes shame on me I'm not using svn for the code, good point.
Anyway it's already released as a gem, why would you prefer a plugin, after all even installing a gem is a one liner...
Post a Comment