adding grouping functionality; added some dummy code ("Schmoin") for experimenting with aggregate joins. need to resolve the ambiguity in the #as operator between (SELECT * FROM foo AS bar) vs. (SELECT * FROM foo) AS bar

15 files changed, 169 insertions, 22 deletions

diff --git a/lib/active_relation/primitives/aggregation.rb b/lib/active_relation/primitives/aggregation.rb
index 48f8946835..26348fb35e 100644
--- a/lib/active_relation/primitives/aggregation.rb
+++ b/lib/active_relation/primitives/aggregation.rb
@@ -6,6 +6,10 @@ module ActiveRelation
       @attribute, @function_sql = attribute, function_sql
     end
 
+    def substitute(new_relation)
+      Aggregation.new(attribute.substitute(new_relation), function_sql)
+    end
+    
     def to_sql(strategy = nil)
       "#{function_sql}(#{attribute.to_sql})"
     end
diff --git a/lib/active_relation/primitives/attribute.rb b/lib/active_relation/primitives/attribute.rb
index 90bbe8012b..eb167b1a66 100644
--- a/lib/active_relation/primitives/attribute.rb
+++ b/lib/active_relation/primitives/attribute.rb
@@ -6,18 +6,25 @@ module ActiveRelation
       @relation, @name, @alias = relation, name, aliaz
     end
 
-    def as(aliaz = nil)
-      Attribute.new(relation, name, aliaz)
-    end
+    module Transformations
+      def as(aliaz = nil)
+        Attribute.new(relation, name, aliaz)
+      end
+    
+      def substitute(new_relation)
+        Attribute.new(new_relation, name, @alias)
+      end
 
+      def qualify
+        self.as(qualified_name)
+      end
+    end
+    include Transformations
+    
     def qualified_name
       "#{relation.name}.#{name}"
     end
 
-    def qualify
-      self.as(qualified_name)
-    end
-
     def ==(other)
       relation == other.relation and name == other.name and @alias == other.alias
     end
diff --git a/lib/active_relation/relations.rb b/lib/active_relation/relations.rb
index 9ae8f16524..c9b9de93b2 100644
--- a/lib/active_relation/relations.rb
+++ b/lib/active_relation/relations.rb
@@ -2,6 +2,8 @@ require 'active_relation/relations/relation'
 require 'active_relation/relations/compound'
 require 'active_relation/relations/table'
 require 'active_relation/relations/join'
+require 'active_relation/relations/schmoin'
+require 'active_relation/relations/group'
 require 'active_relation/relations/projection'
 require 'active_relation/relations/selection'
 require 'active_relation/relations/order'
diff --git a/lib/active_relation/relations/alias.rb b/lib/active_relation/relations/alias.rb
index 63a92ccba1..f40e51475e 100644
--- a/lib/active_relation/relations/alias.rb
+++ b/lib/active_relation/relations/alias.rb
@@ -8,7 +8,7 @@ module ActiveRelation
     end
       
     def attributes
-      relation.attributes.collect(&method(:substitute))
+      relation.attributes.collect { |attribute| attribute.substitute(self) }
     end
 
     def ==(other)
@@ -22,13 +22,8 @@ module ActiveRelation
     
     def attribute(name)
       if unaliased_attribute = relation[name]
-        substitute(unaliased_attribute)
+        unaliased_attribute.substitute(self)
       end
     end
-    
-    private
-    def substitute(attribute)
-      Attribute.new(self, attribute.name, attribute.alias)
-    end
   end
 end
\ No newline at end of file
diff --git a/lib/active_relation/relations/compound.rb b/lib/active_relation/relations/compound.rb
index d6da8eb8fa..bbc7ff1bae 100644
--- a/lib/active_relation/relations/compound.rb
+++ b/lib/active_relation/relations/compound.rb
@@ -2,6 +2,7 @@ module ActiveRelation
   class Compound < Relation
     attr_reader :relation
 
-    delegate :attributes, :attribute, :joins, :selects, :orders, :table_sql, :inserts, :limit, :offset, :to => :relation
+    delegate :attributes, :attribute, :joins, :selects, :orders, :groupings, :table_sql, :inserts, :limit, :offset,
+             :to => :relation
   end
 end
\ No newline at end of file
diff --git a/lib/active_relation/relations/group.rb b/lib/active_relation/relations/group.rb
new file mode 100644
index 0000000000..bc363970c7
--- /dev/null
+++ b/lib/active_relation/relations/group.rb
@@ -0,0 +1,17 @@
+module ActiveRelation
+  class Group < Compound
+    attr_reader :groupings
+
+    def initialize(relation, *groupings)
+      @relation, @groupings = relation, groupings
+    end
+
+    def ==(other)
+      relation == other.relation and groupings == other.groupings
+    end
+
+    def qualify
+      Group.new(relation.qualify, *groupings.collect(&:qualify))
+    end
+  end
+end
\ No newline at end of file
diff --git a/lib/active_relation/relations/order.rb b/lib/active_relation/relations/order.rb
index d5c2a54cd1..688207f7a9 100644
--- a/lib/active_relation/relations/order.rb
+++ b/lib/active_relation/relations/order.rb
@@ -11,7 +11,7 @@ module ActiveRelation
     end
 
     def qualify
-      Order.new(relation.qualify, *orders.collect { |o| o.qualify })
+      Order.new(relation.qualify, *orders.collect(&:qualify))
     end
   end
 end
\ No newline at end of file
diff --git a/lib/active_relation/relations/relation.rb b/lib/active_relation/relations/relation.rb
index 663450e69c..18ba5491b1 100644
--- a/lib/active_relation/relations/relation.rb
+++ b/lib/active_relation/relations/relation.rb
@@ -64,6 +64,10 @@ module ActiveRelation
       def delete
         Deletion.new(self)
       end
+      
+      def group(*attributes)
+        Group.new(self, *attributes)
+      end
   
       JoinOperation = Struct.new(:join_sql, :relation1, :relation2) do
         def on(*predicates)
@@ -80,6 +84,7 @@ module ActiveRelation
         (joins unless joins.blank?),
         ("WHERE #{selects.collect{|s| s.to_sql(Sql::Predicate.new)}.join("\n\tAND ")}" unless selects.blank?),
         ("ORDER BY #{orders.collect(&:to_sql)}" unless orders.blank?),
+        ("GROUP BY #{groupings.collect(&:to_sql)}" unless groupings.blank?),
         ("LIMIT #{limit.to_sql}" unless limit.blank?),
         ("OFFSET #{offset.to_sql}" unless offset.blank?)
       ].compact.join("\n")
@@ -95,6 +100,7 @@ module ActiveRelation
     def selects;    []  end
     def orders;     []  end
     def inserts;    []  end
+    def groupings;  []  end
     def joins;      nil end
     def limit;      nil end
     def offset;     nil end
diff --git a/lib/active_relation/relations/schmoin.rb b/lib/active_relation/relations/schmoin.rb
new file mode 100644
index 0000000000..398cfb7b28
--- /dev/null
+++ b/lib/active_relation/relations/schmoin.rb
@@ -0,0 +1,43 @@
+module ActiveRelation
+  class Schmoin < Relation
+    attr_reader :join_sql, :relation1, :relation2, :predicates
+
+    def initialize(join_sql, relation1, relation2, *predicates)
+      @join_sql, @relation1, @relation2, @predicates = join_sql, relation1, relation2, predicates
+    end
+
+    def ==(other)
+      predicates == other.predicates and
+        ((relation1 == other.relation1 and relation2 == other.relation2) or
+        (relation2 == other.relation1 and relation1 == other.relation2))
+    end
+
+    def qualify
+      Schmoin.new(join_sql, relation1.qualify, relation2.qualify, *predicates.collect(&:qualify))
+    end
+
+    protected
+    def joins
+      [relation1.joins, relation2.joins, join].compact.join(" ")
+    end
+
+    def selects
+      relation1.send(:selects) + relation2.send(:selects)
+    end
+
+    def attributes
+      relation1.attributes + relation2.attributes
+    end
+
+    def attribute(name)
+      relation1[name] || relation2[name]
+    end
+
+    delegate :table_sql, :to => :relation1
+
+    private
+    def join
+      "#{join_sql} #{relation2.send(:table_sql)} ON #{predicates.collect { |p| p.to_sql(Sql::Predicate.new) }.join(' AND ')}"
+    end
+  end
+end
\ No newline at end of file
diff --git a/spec/active_relation/primitives/aggregation_spec.rb b/spec/active_relation/primitives/aggregation_spec.rb
index bdfa152bca..fbd846ffed 100644
--- a/spec/active_relation/primitives/aggregation_spec.rb
+++ b/spec/active_relation/primitives/aggregation_spec.rb
@@ -9,10 +9,17 @@ module ActiveRelation
   
     describe '==' do
       it 'obtains if the attribute and function sql are identical' do
-        @relation1[:id].sum.should == @relation1[:id].sum
-        @relation1[:id].sum.should_not == @relation1[:name].sum
-        @relation1[:id].sum.should_not == @relation1[:name].average 
-        @relation1[:id].sum.should_not == @relation2[:id].sum
+        Aggregation.new(@relation1[:id], "SUM").should == Aggregation.new(@relation1[:id], "SUM")
+        Aggregation.new(@relation1[:id], "SUM").should_not == Aggregation.new(@relation1[:name], "SUM")
+        Aggregation.new(@relation1[:id], "SUM").should_not == Aggregation.new(@relation1[:name], "SUM")
+        Aggregation.new(@relation1[:id], "SUM").should_not == Aggregation.new(@relation2[:id], "SUM")
+      end
+    end
+    
+    describe '#substitute' do
+      it "distributes over the attribute" do
+        Aggregation.new(@relation1[:id], "SUM").substitute(@relation2). \
+          should == Aggregation.new(@relation1[:id].substitute(@relation2), "SUM")
       end
     end
   
diff --git a/spec/active_relation/primitives/attribute_spec.rb b/spec/active_relation/primitives/attribute_spec.rb
index 6078da08f3..543217b9cf 100644
--- a/spec/active_relation/primitives/attribute_spec.rb
+++ b/spec/active_relation/primitives/attribute_spec.rb
@@ -12,6 +12,12 @@ module ActiveRelation
         @relation1[:id].as(:alias).should == Attribute.new(@relation1, :id, :alias)
       end
     end
+    
+    describe '#substitute' do
+      it "manufactures an attribute with the relation substituted" do
+        @relation1[:id].substitute(@relation2).should == Attribute.new(@relation2, :id)
+      end
+    end
   
     describe '#qualified_name' do
       it "manufactures an attribute name prefixed with the relation's name" do
diff --git a/spec/active_relation/relations/group_spec.rb b/spec/active_relation/relations/group_spec.rb
new file mode 100644
index 0000000000..256d7dde45
--- /dev/null
+++ b/spec/active_relation/relations/group_spec.rb
@@ -0,0 +1,29 @@
+require File.join(File.dirname(__FILE__), '..', '..', 'spec_helper')
+
+module ActiveRelation
+  describe Group do
+    before do
+      @relation1 = Table.new(:foo)
+      @relation2 = Table.new(:bar)
+      @attribute1 = @relation1[:id]
+      @attribute2 = @relation2[:id]
+    end
+
+    describe '#qualify' do
+      it "distributes over the relation and attributes" do
+        Group.new(@relation1, @attribute1).qualify. \
+          should == Group.new(@relation1.qualify, @attribute1.qualify)
+      end
+    end
+  
+    describe '#to_sql' do
+      it "manufactures sql with an order clause" do
+        Group.new(@relation1, @attribute1).to_sql.should be_like("""
+          SELECT `foo`.`name`, `foo`.`id`
+          FROM `foo`
+          GROUP BY `foo`.`id`
+        """)
+      end
+    end
+  end
+end
diff --git a/spec/active_relation/relations/relation_spec.rb b/spec/active_relation/relations/relation_spec.rb
index 72b7d14389..1b4ab785b7 100644
--- a/spec/active_relation/relations/relation_spec.rb
+++ b/spec/active_relation/relations/relation_spec.rb
@@ -82,7 +82,13 @@ module ActiveRelation
   
       describe '#order' do
         it "manufactures an order relation" do
-          @relation1.order(@attribute1, @attribute2).should be_kind_of(Order)
+          @relation1.order(@attribute1, @attribute2).should == Order.new(@relation1, @attribute1, @attribute2)
+        end
+      end
+      
+      describe '#group' do
+        it 'manufactures a group relation' do
+          @relation1.group(@attribute1).should == Group.new(@relation1, @attribute1)
         end
       end
     end
diff --git a/spec/active_relation/relations/schmoin_spec.rb b/spec/active_relation/relations/schmoin_spec.rb
new file mode 100644
index 0000000000..14e583afab
--- /dev/null
+++ b/spec/active_relation/relations/schmoin_spec.rb
@@ -0,0 +1,24 @@
+require File.join(File.dirname(__FILE__), '..', '..', 'spec_helper')
+
+module ActiveRelation
+  describe Schmoin do
+    before do
+      @relation = Table.new(:users)
+      photos = Table.new(:photos)
+      @aggregate_relation = photos.project(photos[:user_id], photos[:id].count).group(photos[:user_id]).as(:photo_count)
+      @predicate = Equality.new(@aggregate_relation[:user_id], @relation[:id])
+    end
+  
+    describe '#to_sql' do
+      it 'manufactures sql joining the two tables on the predicate, merging the selects' do
+        pending
+        Schmoin.new("INNER JOIN", @relation, @aggregate_relation, @predicate).to_sql.should be_like("""
+          SELECT `users`.`name`
+          FROM `users`
+            INNER JOIN (SELECT `photos`.`user_id`, count(`photos`.`id`) FROM `photos`) AS `photo_count`
+              ON `photo_count`.`user_id` = `users`.`id`
+        """)
+      end
+    end
+  end
+end
\ No newline at end of file
diff --git a/spec/spec_helper.rb b/spec/spec_helper.rb
index ab9a14f3bd..8f5d76370c 100644
--- a/spec/spec_helper.rb
+++ b/spec/spec_helper.rb
@@ -9,7 +9,7 @@ ActiveRecord::Base.configurations = {
   'sql_algebra_test' => {
     :adapter  => 'mysql',
     :username => 'root',
-    :password => '',
+    :password => 'password',
     :encoding => 'utf8',
     :database => 'sql_algebra_test',
   },