From 851dd0806be4e21f9a4fdcc77162711f46095bc5 Mon Sep 17 00:00:00 2001 From: David Heinemeier Hansson Date: Tue, 18 Oct 2005 12:02:25 +0000 Subject: Added support for using limits in eager loads that involve has_many and has_and_belongs_to_many associations git-svn-id: http://svn-commit.rubyonrails.org/rails/trunk@2675 5ecf4fe2-1ee6-0310-87b1-e25e094e27de --- activerecord/CHANGELOG | 4 ++ activerecord/lib/active_record/associations.rb | 63 +++++++++++++++++++++---- activerecord/test/associations_go_eager_test.rb | 31 +++++++++++- 3 files changed, 87 insertions(+), 11 deletions(-) (limited to 'activerecord') diff --git a/activerecord/CHANGELOG b/activerecord/CHANGELOG index 38ff390a46..8be783c5fc 100644 --- a/activerecord/CHANGELOG +++ b/activerecord/CHANGELOG @@ -1,3 +1,7 @@ +*1.12.1* + +* Added support for using limits in eager loads that involve has_many and has_and_belongs_to_many associations + *1.12.0* (October 16th, 2005) * Update/clean up documentation (rdoc) diff --git a/activerecord/lib/active_record/associations.rb b/activerecord/lib/active_record/associations.rb index 39060ae972..cf015f89db 100755 --- a/activerecord/lib/active_record/associations.rb +++ b/activerecord/lib/active_record/associations.rb @@ -167,9 +167,14 @@ module ActiveRecord # the number of queries. The database still needs to send all the data to Active Record and it still needs to be processed. So its no # catch-all for performance problems, but its a great way to cut down on the number of queries in a situation as the one described above. # - # Please note that because eager loading is fetching both models and associations in the same grab, it doesn't make sense to use the - # :limit and :offset options on has_many and has_and_belongs_to_many associations and an ConfigurationError exception will be raised - # if attempted. It does, however, work just fine with has_one and belongs_to associations. + # Please note that limited eager loading with has_many and has_and_belongs_to_many associations is not compatible with describing conditions + # on these eager tables. This will work: + # + # Post.find(:all, :include => :comments, :conditions => "posts.title = 'magic forest'", :limit => 2) + # + # ...but this will not (and an ArgumentError will be raised): + # + # Post.find(:all, :include => :comments, :conditions => "comments.body like 'Normal%'", :limit => 2) # # Also have in mind that since the eager loading is pulling from multiple tables, you'll have to disambiguate any column references # in both conditions and orders. So :order => "posts.id DESC" will work while :order => "id DESC" will not. This may require that @@ -766,7 +771,6 @@ module ActiveRecord reflections = reflect_on_included_associations(options[:include]) guard_against_missing_reflections(reflections, options) - guard_against_unlimitable_reflections(reflections, options) schema_abbreviations = generate_schema_abbreviations(reflections) primary_key_table = generate_primary_key_table(reflections, schema_abbreviations) @@ -867,24 +871,61 @@ module ActiveRecord sql = "SELECT #{column_aliases(schema_abbreviations)} FROM #{table_name} " sql << reflections.collect { |reflection| association_join(reflection) }.to_s sql << "#{options[:joins]} " if options[:joins] + add_conditions!(sql, options[:conditions]) add_sti_conditions!(sql, reflections) + add_limited_ids_condition!(sql, options) if !using_limitable_reflections?(reflections) && options[:limit] + sql << "ORDER BY #{options[:order]} " if options[:order] + add_limit!(sql, options) if using_limitable_reflections?(reflections) + return sanitize_sql(sql) end + def add_limited_ids_condition!(sql, options) + unless (id_list = select_limited_ids_list(options)).empty? + sql << "#{condition_word(sql)} #{table_name}.#{primary_key} IN (#{id_list}) " + end + end + + def select_limited_ids_list(options) + connection.select_values( + construct_finder_sql_for_association_limiting(options), + "#{name} Load IDs For Limited Eager Loading" + ).collect { |id| "'#{id}'" }.join(", ") + end + + def construct_finder_sql_for_association_limiting(options) + raise(ArgumentError, "Limited eager loads and conditions on the eager tables is incompatible") if include_eager_conditions?(options) + + sql = "SELECT #{primary_key} FROM #{table_name} " + add_conditions!(sql, options[:conditions]) + sql << "ORDER BY #{options[:order]} " if options[:order] + add_limit!(sql, options) + return sanitize_sql(sql) + end + + def include_eager_conditions?(options) + return false unless options[:conditions] + + options[:conditions].scan(/ ([^.]+)\.[^.]+ /).flatten.any? do |condition_table_name| + condition_table_name != table_name + end + end + def using_limitable_reflections?(reflections) reflections.reject { |r| [ :belongs_to, :has_one ].include?(r.macro) }.length.zero? end def add_sti_conditions!(sql, reflections) - sti_sql = "" - reflections.each do |reflection| - sti_sql << " AND #{reflection.klass.send(:type_condition)}" unless reflection.klass.descends_from_active_record? + sti_conditions = reflections.collect do |reflection| + reflection.klass.send(:type_condition) unless reflection.klass.descends_from_active_record? + end.compact + + unless sti_conditions.empty? + sql << condition_word(sql) + sti_conditions.join(" AND ") end - sti_sql.sub!(/AND/, "WHERE") unless sql =~ /where/i - sql << sti_sql end def column_aliases(schema_abbreviations) @@ -933,6 +974,10 @@ module ActiveRecord end return record end + + def condition_word(sql) + sql =~ /where/i ? " AND " : "WHERE " + end end end diff --git a/activerecord/test/associations_go_eager_test.rb b/activerecord/test/associations_go_eager_test.rb index ec088eebb7..3dccc8e65a 100644 --- a/activerecord/test/associations_go_eager_test.rb +++ b/activerecord/test/associations_go_eager_test.rb @@ -95,8 +95,35 @@ class EagerAssociationTest < Test::Unit::TestCase assert_equal [], posts end - def test_eager_association_raise_on_limit - assert_raises(ActiveRecord::ConfigurationError) { Post.find(:all, :include => [:author, :comments], :limit => 1) } + def test_eager_with_has_many_and_limit + posts = Post.find(:all, :include => [ :author, :comments ], :limit => 2) + assert_equal 2, posts.size + assert_equal 3, posts.inject(0) { |sum, post| sum += post.comments.size } + end + + def test_eager_with_has_many_and_limit_with_no_results + posts = Post.find(:all, :include => [ :author, :comments ], :limit => 2, :conditions => "posts.title = 'magic forest'") + assert_equal 0, posts.size + end + + def test_eager_with_has_and_belongs_to_many_and_limit + posts = Post.find(:all, :include => :categories, :order => "posts.id", :limit => 3) + assert_equal 3, posts.size + assert_equal 2, posts[0].categories.size + assert_equal 1, posts[1].categories.size + assert_equal 0, posts[2].categories.size + assert posts[0].categories.include?(categories(:technology)) + assert posts[1].categories.include?(categories(:general)) + end + + def test_eager_with_has_many_and_limit_and_conditions_on_the_eagers + assert_raises(ArgumentError) do + posts = authors(:david).posts.find(:all, + :include => :comments, + :conditions => "comments.body like 'Normal%' OR comments.type = 'SpecialComment'", + :limit => 2 + ) + end end def test_eager_association_loading_with_habtm -- cgit v1.2.3