I (obviously) agree with Steven, Joshua and Gabe, not only is this
common (in any large app), but I don't see any performance issue with
it (on the contrary). I also think this kind of nesting is the logical
thing to support, as I've seen many users think (myself included) that
it was already there in Rails, try it and be surprised with the errors
it caused. If the Rails way was wanting the user to do custom
"optimized" queries all the time, perhaps it shouldn't even have
associations or finders.
In any reasonably large DB with more than 50 entities it is impossible
to model things in a way that you will never need more than 2 levels
of "INNER JOINING" to query things. So, I agree that the examples
given here may be a case of "bad modeling" (perhaps not bad, but that
could be handled in some other way) if considered separately. My real
app that needs this is way more complex than the example given, I just
made this example up to try to explain it faster. By supporting HMT
nesting Rails will not stimulate (sane) users to make their DB
unnecessarily more complex, but will help (a lot) developer of larger
apps, who can't avoid complexity.
I coded my example ('posts and comments') and run some benchmarks on
it. I implemented some finders in the user class, in 5 different ways.
The last one is just using the nested HMT plugin from the patch (I
needlessly put it in a method as well, just to say I wasn't being
unfair in the benchmark).
def ratings_ruby_way(*arguments_to_find)
ratings = comments.find(:all).inject() do |arr, c|
arr << c.comment_ratings.find(*arguments_to_find)
end
return ratings.flatten
end
def ratings_include_ruby_way(*arguments_to_find)
ratings =
comments.find(:all, :include=>:comment_ratings).inject()do |arr, c|
arr << c.comment_ratings.find(*arguments_to_find)
end
return ratings.flatten
end
def ratings_find_all_include_ruby_way
ratings =
comments.find(:all, :include=>:comment_ratings).inject()do |arr, c|
arr << c.comment_ratings
end
return ratings.flatten
end
def ratings_find_all_by_sql_way
CommentRating.find_by_sql(
[%q{
Select comment_ratings.* FROM comment_ratings
INNER JOIN comments ON
comments.id = comment_ratings.comment_id
INNER JOIN posts ON
posts.id = comments.post_id
INNER JOIN users ON
users.id = posts.user_id
WHERE
users.id = ?},
self.id] )
end
def ratings_nested_way(*arguments_to_find)
ratings = comment_ratings.find(*arguments_to_find)
return ratings
end
I've filled the tables with some test data (about 700 rows each, 5000
comment_ratings). In the benchmark each method is run 500 times,
passing ':all' when appropriate, on a set of 500 random users (the
same set for each method, on the same run).
Results for PostgreSQL 8.2 (using pg dlls based adapter):
load 'lib/benchmark_hmt.rb'
user system total real
ruby_way 1.172000 0.422000 1.594000 ( 3.328000)
ruby_include 2.562000 0.484000 3.046000 ( 5.625000)
find_all_ruby_include 2.172000 0.203000 2.375000 ( 4.078000)
find_all_sql 0.734000 0.203000 0.937000 ( 2.156000)
nested 1.016000 0.266000 1.282000 ( 2.360000)
Results for MySQL 5.0.24a (with default Rails adapter):
load 'lib/benchmark_hmt.rb'
user system total real
ruby_way 2.766000 0.235000 3.001000 ( 3.407000)
ruby_include 4.703000 0.422000 5.125000 ( 7.656000)
find_all_ruby_include 3.453000 0.109000 3.562000 ( 3.891000)
find_all_sql 1.485000 0.250000 1.735000 ( 1.937000)
nested 1.812000 0.234000 2.046000 ( 2.250000)
It was run on a Pentium M 1.7GHZ, 1GB RAM, WinXP. The DBMSs were
local. I used Rails Edge (from yesterday).
Even though MySQL was slightly faster, when I initially tried it on a
larger set of test data (~ 20000 rows) it was slower than Postgres.
The methods with :include got unusable, and only one query was taking
more than 160 seconds (they virtually froze my notebook). MySQL didn't
like the mix of LEFT OUTER JOIN and INNER JOIN Rails generated. I
don't have the benchmark numbers for those tests and it takes too long
to enter the test data, specially in MySQL, so I decided to use this
smaller set.
The great surprise here is that using :include was slower than just
making a lot of smaller queries (method 1 versus method 3). Using the
nested has_many :through is almost as fast as doing the find_by_sql
directly, and way faster than anything else. And the great plus of the
nested has_many :through is that you get all those fine association
methods, effortlessly.
If you want check the benchmark app, I made it available at:
http://www.bernardopadua.com/temp/benchmark_nested_has_many_through.zip
Cheers,
Bernardo