Add ActiveRecord::Relation#uniq for toggling DISTINCT in the SQL query

9 files changed, 89 insertions, 13 deletions

diff --git a/Gemfile b/Gemfile
index 4ac6bf764d..f94f267f05 100644
--- a/Gemfile
+++ b/Gemfile
@@ -4,6 +4,8 @@ gemspec
 
 if ENV['AREL']
   gem "arel", :path => ENV['AREL']
+else
+  gem "arel", :git => "git://github.com/rails/arel"
 end
 
 gem "bcrypt-ruby", "~> 3.0.0"
diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 426f6b48b3..2524082062 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,5 +1,21 @@
 ## Rails 3.2.0 (unreleased) ##
 
+*   Add ActiveRecord::Relation#uniq for generating unique queries.
+
+    Before:
+
+        Client.select('DISTINCT name')
+
+    After:
+
+        Client.select(:name).uniq
+
+    This also allows you to revert the unqueness in a relation:
+
+        Client.select(:name).uniq.uniq(false)
+
+    *Jon Leighton*
+
 *   Support index sort order in sqlite, mysql and postgres adapters. *Vlad Jebelev*
 
 *   Allow the :class_name option for associations to take a symbol (:Client) in addition to
diff --git a/activerecord/lib/active_record/base.rb b/activerecord/lib/active_record/base.rb
index 360e494af1..455b299270 100644
--- a/activerecord/lib/active_record/base.rb
+++ b/activerecord/lib/active_record/base.rb
@@ -445,7 +445,9 @@ module ActiveRecord #:nodoc:
       delegate :first_or_create, :first_or_create!, :first_or_initialize, :to => :scoped
       delegate :destroy, :destroy_all, :delete, :delete_all, :update, :update_all, :to => :scoped
       delegate :find_each, :find_in_batches, :to => :scoped
-      delegate :select, :group, :order, :except, :reorder, :limit, :offset, :joins, :where, :preload, :eager_load, :includes, :from, :lock, :readonly, :having, :create_with, :to => :scoped
+      delegate :select, :group, :order, :except, :reorder, :limit, :offset, :joins,
+               :where, :preload, :eager_load, :includes, :from, :lock, :readonly,
+               :having, :create_with, :uniq, :to => :scoped
       delegate :count, :average, :minimum, :maximum, :sum, :calculate, :to => :scoped
 
       # Executes a custom SQL query against your database and returns all the results. The results will
diff --git a/activerecord/lib/active_record/relation.rb b/activerecord/lib/active_record/relation.rb
index ecefaa633c..3baf9b3f49 100644
--- a/activerecord/lib/active_record/relation.rb
+++ b/activerecord/lib/active_record/relation.rb
@@ -7,7 +7,7 @@ module ActiveRecord
     JoinOperation = Struct.new(:relation, :join_class, :on)
     ASSOCIATION_METHODS = [:includes, :eager_load, :preload]
     MULTI_VALUE_METHODS = [:select, :group, :order, :joins, :where, :having, :bind]
-    SINGLE_VALUE_METHODS = [:limit, :offset, :lock, :readonly, :from, :reorder, :reverse_order]
+    SINGLE_VALUE_METHODS = [:limit, :offset, :lock, :readonly, :from, :reorder, :reverse_order, :uniq]
 
     include FinderMethods, Calculations, SpawnMethods, QueryMethods, Batches
 
diff --git a/activerecord/lib/active_record/relation/query_methods.rb b/activerecord/lib/active_record/relation/query_methods.rb
index 670ba0987d..c281bead0d 100644
--- a/activerecord/lib/active_record/relation/query_methods.rb
+++ b/activerecord/lib/active_record/relation/query_methods.rb
@@ -9,7 +9,8 @@ module ActiveRecord
                   :select_values, :group_values, :order_values, :joins_values,
                   :where_values, :having_values, :bind_values,
                   :limit_value, :offset_value, :lock_value, :readonly_value, :create_with_value,
-                  :from_value, :reorder_value, :reverse_order_value
+                  :from_value, :reorder_value, :reverse_order_value,
+                  :uniq_value
 
     def includes(*args)
       args.reject! {|a| a.blank? }
@@ -38,7 +39,7 @@ module ActiveRecord
     end
 
     # Works in two unique ways.
-    # 
+    #
     # First: takes a block so it can be used just like Array#select.
     #
     #   Model.scoped.select { |m| m.field == value }
@@ -176,9 +177,25 @@ module ActiveRecord
       relation
     end
 
+    # Specifies whether the records should be unique or not. For example:
+    #
+    #   User.select(:name)
+    #   # => Might return two records with the same name
+    #
+    #   User.select(:name).uniq
+    #   # => Returns 1 record per unique name
+    #
+    #   User.select(:name).uniq.uniq(false)
+    #   # => You can also remove the uniqueness
+    def uniq(value = true)
+      relation = clone
+      relation.uniq_value = value
+      relation
+    end
+
     # Used to extend a scope with additional methods, either through
-    # a module or through a block provided. 
-    # 
+    # a module or through a block provided.
+    #
     # The object returned is a relation, which can be further extended.
     #
     # === Using a module
@@ -200,7 +217,7 @@ module ActiveRecord
     #
     #   scope = Model.scoped.extending do
     #     def page(number)
-    #       # pagination code goes here 
+    #       # pagination code goes here
     #     end
     #   end
     #   scope.page(params[:page])
@@ -209,7 +226,7 @@ module ActiveRecord
     #
     #   scope = Model.scoped.extending(Pagination) do
     #     def per_page(number)
-    #       # pagination code goes here 
+    #       # pagination code goes here
     #     end
     #   end
     def extending(*modules)
@@ -252,6 +269,7 @@ module ActiveRecord
 
       build_select(arel, @select_values.uniq)
 
+      arel.distinct(@uniq_value)
       arel.from(@from_value) if @from_value
       arel.lock(@lock_value) if @lock_value
 
diff --git a/activerecord/test/cases/base_test.rb b/activerecord/test/cases/base_test.rb
index 12c1cfb30e..fdb656fe13 100644
--- a/activerecord/test/cases/base_test.rb
+++ b/activerecord/test/cases/base_test.rb
@@ -1935,4 +1935,10 @@ class BasicsTest < ActiveRecord::TestCase
     dev.update_attribute(:updated_at, nil)
     assert_match(/\/#{dev.id}$/, dev.cache_key)
   end
+
+  def test_uniq_delegates_to_scoped
+    scope = stub
+    Bird.stubs(:scoped).returns(mock(:uniq => scope))
+    assert_equal scope, Bird.uniq
+  end
 end
diff --git a/activerecord/test/cases/relation_test.rb b/activerecord/test/cases/relation_test.rb
index b23ead6feb..715a378431 100644
--- a/activerecord/test/cases/relation_test.rb
+++ b/activerecord/test/cases/relation_test.rb
@@ -20,7 +20,7 @@ module ActiveRecord
     end
 
     def test_single_values
-      assert_equal [:limit, :offset, :lock, :readonly, :from, :reorder, :reverse_order].map(&:to_s).sort,
+      assert_equal [:limit, :offset, :lock, :readonly, :from, :reorder, :reverse_order, :uniq].map(&:to_s).sort,
         Relation::SINGLE_VALUE_METHODS.map(&:to_s).sort
     end
 
diff --git a/activerecord/test/cases/relations_test.rb b/activerecord/test/cases/relations_test.rb
index 95408a5f29..bf1eb6386a 100644
--- a/activerecord/test/cases/relations_test.rb
+++ b/activerecord/test/cases/relations_test.rb
@@ -1148,4 +1148,20 @@ class RelationTest < ActiveRecord::TestCase
     assert_equal posts(:thinking), comments(:more_greetings).post
     assert_equal posts(:welcome),  comments(:greetings).post
   end
+
+  def test_uniq
+    tag1 = Tag.create(:name => 'Foo')
+    tag2 = Tag.create(:name => 'Foo')
+
+    query = Tag.select(:name).where(:id => [tag1.id, tag2.id])
+
+    assert_equal ['Foo', 'Foo'], query.map(&:name)
+    assert_sql(/DISTINCT/) do
+      assert_equal ['Foo'], query.uniq.map(&:name)
+    end
+    assert_sql(/DISTINCT/) do
+      assert_equal ['Foo'], query.uniq(true).map(&:name)
+    end
+    assert_equal ['Foo', 'Foo'], query.uniq(true).uniq(false).map(&:name)
+  end
 end
diff --git a/railties/guides/source/active_record_querying.textile b/railties/guides/source/active_record_querying.textile
index 2e1f89cb78..0ad2644095 100644
--- a/railties/guides/source/active_record_querying.textile
+++ b/railties/guides/source/active_record_querying.textile
@@ -201,7 +201,7 @@ end
 
 But this approach becomes increasingly impractical as the table size increases, since +User.all.each+ instructs Active Record to fetch _the entire table_ in a single pass, build a model object per row, and then keep the entire array of model objects in memory. Indeed, if we have a large number of records, the entire collection may exceed the amount of memory available.
 
-Rails provides two methods that address this problem by dividing records into memory-friendly batches for processing. The first method, +find_each+, retrieves a batch of records and then yields _each_ record to the block individually as a model. The second method, +find_in_batches+, retrieves a batch of records and then yields _the entire batch_ to the block as an array of models. 
+Rails provides two methods that address this problem by dividing records into memory-friendly batches for processing. The first method, +find_each+, retrieves a batch of records and then yields _each_ record to the block individually as a model. The second method, +find_in_batches+, retrieves a batch of records and then yields _the entire batch_ to the block as an array of models.
 
 TIP: The +find_each+ and +find_in_batches+ methods are intended for use in the batch processing of a large number of records that wouldn't fit in memory all at once. If you just need to loop over a thousand records the regular find methods are the preferred option.
 
@@ -435,10 +435,26 @@ ActiveModel::MissingAttributeError: missing attribute: <attribute>
 
 Where +&lt;attribute&gt;+ is the attribute you asked for. The +id+ method will not raise the +ActiveRecord::MissingAttributeError+, so just be careful when working with associations because they need the +id+ method to function properly.
 
-You can also call SQL functions within the select option. For example, if you would like to only grab a single record per unique value in a certain field by using the +DISTINCT+ function you can do it like this:
+If you would like to only grab a single record per unique value in a certain field, you can use +uniq+:
 
 <ruby>
-Client.select("DISTINCT(name)")
+Client.select(:name).uniq
+</ruby>
+
+This would generate SQL like:
+
+<sql>
+SELECT DISTINCT name FROM clients
+</sql>
+
+You can also remove the uniqueness constraint:
+
+<ruby>
+query = Client.select(:name).uniq
+# => Returns unique names
+
+query.uniq(false)
+# => Returns all names, even if there are duplicates
 </ruby>
 
 h3. Limit and Offset
@@ -741,7 +757,7 @@ SELECT categories.* FROM categories
   INNER JOIN posts ON posts.category_id = categories.id
 </sql>
 
-Or, in English: "return a Category object for all categories with posts". Note that you will see duplicate categories if more than one post has the same category. If you want unique categories, you can use Category.joins(:post).select("distinct(categories.id)"). 
+Or, in English: "return a Category object for all categories with posts". Note that you will see duplicate categories if more than one post has the same category. If you want unique categories, you can use Category.joins(:post).select("distinct(categories.id)").
 
 h5. Joining Multiple Associations