Merge pull request #12071 from Crunch09/outer_joins

added ActiveRecord::Relation#outer_joins

7 files changed, 176 insertions, 16 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index bee9a099af..82d0c91c58 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*
+
 *   Support passing an array to `order` for SQL parameter sanitization.
 
     *Aaron Suggs*
diff --git a/activerecord/lib/active_record/associations/join_dependency.rb b/activerecord/lib/active_record/associations/join_dependency.rb
index 0e98a3b3a4..9f183c3e7e 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|
@@ -176,6 +181,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 87a1988f2f..7ab2108de0 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, :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 392b462aa9..ad7ea199d6 100644
--- a/activerecord/lib/active_record/relation.rb
+++ b/activerecord/lib/active_record/relation.rb
@@ -4,7 +4,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 ad6c7fa2e5..27e7948b10 100644
--- a/activerecord/lib/active_record/relation/query_methods.rb
+++ b/activerecord/lib/active_record/relation/query_methods.rb
@@ -428,6 +428,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.
     #
@@ -878,6 +897,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?
@@ -937,6 +957,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
@@ -952,6 +985,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]
@@ -967,7 +1005,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
diff --git a/guides/source/active_record_querying.md b/guides/source/active_record_querying.md
index 1427903dfb..b7773ea65a 100644
--- a/guides/source/active_record_querying.md
+++ b/guides/source/active_record_querying.md
@@ -69,6 +69,7 @@ The methods are:
 * `having`
 * `includes`
 * `joins`
+* `left_outer_joins`
 * `limit`
 * `lock`
 * `none`
@@ -935,25 +936,30 @@ end
 Joining Tables
 --------------
 
-Active Record provides a finder method called `joins` for specifying `JOIN` clauses on the resulting SQL. There are multiple ways to use the `joins` method.
+Active Record provides two finder methods for specifying `JOIN` clauses on the
+resulting SQL: `joins` and `left_outer_joins`.
+While `joins` should be used for `INNER JOIN` or custom queries,
+`left_outer_joins` is used for queries using `LEFT OUTER JOIN`.
 
-### Using a String SQL Fragment
+### `joins`
+
+There are multiple ways to use the `joins` method.
+
+#### Using a String SQL Fragment
 
 You can just supply the raw SQL specifying the `JOIN` clause to `joins`:
 
 ```ruby
-Client.joins('LEFT OUTER JOIN addresses ON addresses.client_id = clients.id')
+Author.joins("INNER JOIN posts ON posts.author_id = author.id AND posts.published = 't'")
 ```
 
 This will result in the following SQL:
 
 ```sql
-SELECT clients.* FROM clients LEFT OUTER JOIN addresses ON addresses.client_id = clients.id
+SELECT clients.* FROM clients INNER JOIN posts ON posts.author_id = author.id AND posts.published = 't'
 ```
 
-### Using Array/Hash of Named Associations
-
-WARNING: This method only works with `INNER JOIN`.
+#### Using Array/Hash of Named Associations
 
 Active Record lets you use the names of the [associations](association_basics.html) defined on the model as a shortcut for specifying `JOIN` clauses for those associations when using the `joins` method.
 
@@ -986,7 +992,7 @@ end
 
 Now all of the following will produce the expected join queries using `INNER JOIN`:
 
-#### Joining a Single Association
+##### Joining a Single Association
 
 ```ruby
 Category.joins(:articles)
@@ -1017,7 +1023,7 @@ SELECT articles.* FROM articles
 
 Or, in English: "return all articles that have a category and at least one comment". Note again that articles with multiple comments will show up multiple times.
 
-#### Joining Nested Associations (Single Level)
+##### Joining Nested Associations (Single Level)
 
 ```ruby
 Article.joins(comments: :guest)
@@ -1033,7 +1039,7 @@ SELECT articles.* FROM articles
 
 Or, in English: "return all articles that have a comment made by a guest."
 
-#### Joining Nested Associations (Multiple Level)
+##### Joining Nested Associations (Multiple Level)
 
 ```ruby
 Category.joins(articles: [{ comments: :guest }, :tags])
@@ -1049,7 +1055,7 @@ SELECT categories.* FROM categories
   INNER JOIN tags ON tags.article_id = articles.id
 ```
 
-### Specifying Conditions on the Joined Tables
+#### Specifying Conditions on the Joined Tables
 
 You can specify conditions on the joined tables using the regular [Array](#array-conditions) and [String](#pure-string-conditions) conditions. [Hash conditions](#hash-conditions) provide a special syntax for specifying conditions for the joined tables:
 
@@ -1067,6 +1073,26 @@ Client.joins(:orders).where(orders: { created_at: time_range })
 
 This will find all clients who have orders that were created yesterday, again using a `BETWEEN` SQL expression.
 
+### `left_outer_joins`
+
+If you want to select a set of records whether or not they have associated
+records you can use the `left_outer_joins` method.
+
+```ruby
+Author.left_outer_joins(:posts).uniq.select('authors.*, COUNT(posts.*) AS posts_count').group('authors.id')
+```
+
+Which produces:
+
+```sql
+SELECT DISTINCT authors.*, COUNT(posts.*) AS posts_count FROM "authors"
+LEFT OUTER JOIN posts ON posts.author_id = authors.id GROUP BY authors.id
+```
+
+Which means: "return all authors with their count of posts, whether or not they
+have any posts at all"
+
+
 Eager Loading Associations
 --------------------------