From 3f46ef1ddab87482b730a3f53987e04308783d8b Mon Sep 17 00:00:00 2001 From: Florian Thomas Date: Fri, 2 Aug 2013 14:52:02 +0200 Subject: added ActiveRecord::Relation#left_outer_joins Example: User.left_outer_joins(:posts) => SELECT "users".* FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id" --- activerecord/CHANGELOG.md | 9 +++ .../active_record/associations/join_dependency.rb | 17 ++++- activerecord/lib/active_record/querying.rb | 2 +- activerecord/lib/active_record/relation.rb | 2 +- .../lib/active_record/relation/query_methods.rb | 40 +++++++++++- .../left_outer_join_association_test.rb | 74 ++++++++++++++++++++++ 6 files changed, 139 insertions(+), 5 deletions(-) create mode 100644 activerecord/test/cases/associations/left_outer_join_association_test.rb (limited to 'activerecord') diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md index 5b09101bca..f933f861b8 100644 --- a/activerecord/CHANGELOG.md +++ b/activerecord/CHANGELOG.md @@ -1,3 +1,12 @@ +* Added `ActiveRecord::Relation#left_outer_joins`. + + Example: + User.left_outer_joins(:posts) + => SELECT "users".* FROM "users" LEFT OUTER JOIN "posts" ON + "posts"."user_id" = "users"."id" + + *Florian Thomas* + * Allow single table inheritance instantiation to work when storing demodulized class names. diff --git a/activerecord/lib/active_record/associations/join_dependency.rb b/activerecord/lib/active_record/associations/join_dependency.rb index 81eb5136a1..a99adbeee1 100644 --- a/activerecord/lib/active_record/associations/join_dependency.rb +++ b/activerecord/lib/active_record/associations/join_dependency.rb @@ -103,9 +103,14 @@ module ActiveRecord join_root.drop(1).map!(&:reflection) end - def join_constraints(outer_joins) + def join_constraints(outer_joins, join_type) joins = join_root.children.flat_map { |child| - make_inner_joins join_root, child + + if join_type == Arel::Nodes::OuterJoin + make_left_outer_joins join_root, child + else + make_inner_joins join_root, child + end } joins.concat outer_joins.flat_map { |oj| @@ -175,6 +180,14 @@ module ActiveRecord [info] + child.children.flat_map { |c| make_outer_joins(child, c) } end + def make_left_outer_joins(parent, child) + tables = child.tables + join_type = Arel::Nodes::OuterJoin + info = make_constraints parent, child, tables, join_type + + [info] + child.children.flat_map { |c| make_left_outer_joins(child, c) } + end + def make_inner_joins(parent, child) tables = child.tables join_type = Arel::Nodes::InnerJoin diff --git a/activerecord/lib/active_record/querying.rb b/activerecord/lib/active_record/querying.rb index 4e597590e9..b769c90184 100644 --- a/activerecord/lib/active_record/querying.rb +++ b/activerecord/lib/active_record/querying.rb @@ -7,7 +7,7 @@ module ActiveRecord delegate :find_by, :find_by!, to: :all delegate :destroy, :destroy_all, :delete, :delete_all, :update, :update_all, to: :all delegate :find_each, :find_in_batches, to: :all - delegate :select, :group, :order, :except, :reorder, :limit, :offset, :joins, :or, + delegate :select, :group, :order, :except, :reorder, :limit, :offset, :joins, :left_outer_joins, :or, :where, :rewhere, :preload, :eager_load, :includes, :from, :lock, :readonly, :having, :create_with, :uniq, :distinct, :references, :none, :unscope, to: :all delegate :count, :average, :minimum, :maximum, :sum, :calculate, to: :all diff --git a/activerecord/lib/active_record/relation.rb b/activerecord/lib/active_record/relation.rb index 85648a7f8f..c0338600af 100644 --- a/activerecord/lib/active_record/relation.rb +++ b/activerecord/lib/active_record/relation.rb @@ -5,7 +5,7 @@ module ActiveRecord # = Active Record Relation class Relation MULTI_VALUE_METHODS = [:includes, :eager_load, :preload, :select, :group, - :order, :joins, :references, + :order, :joins, :left_outer_joins, :references, :extending, :unscope] SINGLE_VALUE_METHODS = [:limit, :offset, :lock, :readonly, :reordering, diff --git a/activerecord/lib/active_record/relation/query_methods.rb b/activerecord/lib/active_record/relation/query_methods.rb index 69ce5cdc2a..91ce6bd384 100644 --- a/activerecord/lib/active_record/relation/query_methods.rb +++ b/activerecord/lib/active_record/relation/query_methods.rb @@ -431,6 +431,25 @@ module ActiveRecord self end + # Performs a left outer joins on +args+: + # + # User.left_outer_joins(:posts) + # => SELECT "users".* FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id" + # + def left_outer_joins(*args) + check_if_method_has_arguments!(:left_outer_joins, args) + + args.compact! + args.flatten! + + spawn.left_outer_joins!(*args) + end + + def left_outer_joins!(*args) # :nodoc: + self.left_outer_joins_values += args + self + end + # Returns a new relation, which is the result of filtering the current relation # according to the conditions in the arguments. # @@ -882,6 +901,7 @@ module ActiveRecord arel = Arel::SelectManager.new(table) build_joins(arel, joins_values.flatten) unless joins_values.empty? + build_left_outer_joins(arel, left_outer_joins_values.flatten) unless left_outer_joins_values.empty? arel.where(where_clause.ast) unless where_clause.empty? arel.having(having_clause.ast) unless having_clause.empty? @@ -941,6 +961,19 @@ module ActiveRecord end end + def build_left_outer_joins(manager, outer_joins) + buckets = outer_joins.group_by do |join| + case join + when Hash, Symbol, Array + :association_join + else + raise ArgumentError, 'only Hash, Symbol and Array are allowed' + end + end + + build_join_query(manager, buckets, Arel::Nodes::OuterJoin) + end + def build_joins(manager, joins) buckets = joins.group_by do |join| case join @@ -956,6 +989,11 @@ module ActiveRecord raise 'unknown class: %s' % join.class.name end end + + build_join_query(manager, buckets, Arel::Nodes::InnerJoin) + end + + def build_join_query(manager, buckets, join_type) buckets.default = [] association_joins = buckets[:association_join] @@ -971,7 +1009,7 @@ module ActiveRecord join_list ) - join_infos = join_dependency.join_constraints stashed_association_joins + join_infos = join_dependency.join_constraints stashed_association_joins, join_type join_infos.each do |info| info.joins.each { |join| manager.from(join) } diff --git a/activerecord/test/cases/associations/left_outer_join_association_test.rb b/activerecord/test/cases/associations/left_outer_join_association_test.rb new file mode 100644 index 0000000000..a362b43cc3 --- /dev/null +++ b/activerecord/test/cases/associations/left_outer_join_association_test.rb @@ -0,0 +1,74 @@ +require "cases/helper" +require 'models/post' +require 'models/comment' +require 'models/author' +require 'models/essay' +require 'models/categorization' +require 'models/person' + +class LeftOuterJoinAssociationTest < ActiveRecord::TestCase + fixtures :authors, :essays, :posts, :comments, :categorizations, :people + + def test_construct_finder_sql_applies_aliases_tables_on_association_conditions + result = Author.left_outer_joins(:thinking_posts, :welcome_posts).to_a + assert_equal authors(:david), result.first + end + + def test_construct_finder_sql_does_not_table_name_collide_on_duplicate_associations + assert_nothing_raised do + sql = capture_sql do + Person.left_outer_joins(:agents => {:agents => :agents}) + .left_outer_joins(:agents => {:agents => {:primary_contact => :agents}}).to_a + end.first + assert_match(/agents_people_4/i, sql) + end + end + + def test_construct_finder_sql_executes_a_left_outer_join + assert_not_equal Author.count, Author.joins(:posts).count + assert_equal Author.count, Author.left_outer_joins(:posts).count + end + + def test_construct_finder_sql_ignores_empty_left_outer_joins_hash + sql = capture_sql { Author.left_outer_joins({}) }.first + assert_no_match(/LEFT OUTER JOIN/i, sql) + end + + def test_construct_finder_sql_ignores_empty_left_outer_joins_array + sql = capture_sql { Author.left_outer_joins([]) }.first + assert_no_match(/LEFT OUTER JOIN/i, sql) + end + + def test_left_outer_joins_forbids_to_use_string_as_argument + assert_raise(ArgumentError){ Author.left_outer_joins('LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"').to_a } + end + + def test_join_conditions_added_to_join_clause + sql = capture_sql { Author.left_outer_joins(:essays).to_a }.first + assert_match(/writer_type.*?=.*?(Author|\?|\$1)/i, sql) + assert_no_match(/WHERE/i, sql) + end + + def test_find_with_sti_join + scope = Post.left_outer_joins(:special_comments).where(:id => posts(:sti_comments).id) + + # The join should match SpecialComment and its subclasses only + assert scope.where("comments.type" => "Comment").empty? + assert !scope.where("comments.type" => "SpecialComment").empty? + assert !scope.where("comments.type" => "SubSpecialComment").empty? + end + + def test_does_not_override_select + authors = Author.select("authors.name, #{%{(authors.author_address_id || ' ' || authors.author_address_extra_id) as addr_id}}").left_outer_joins(:posts) + assert authors.any? + assert authors.first.respond_to?(:addr_id) + end + + test "the default scope of the target is applied when joining associations" do + author = Author.create! name: "Jon" + author.categorizations.create! + author.categorizations.create! special: true + + assert_equal [author], Author.where(id: author).left_outer_joins(:special_categorizations) + end +end -- cgit v1.2.3