ActiveRecord::Relation::Calculations#count fails on grouping with select

count_with_group_and_select.rb

gem 'activerecord', '4.0.0.beta'
require 'active_record'
require 'logger'

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.logger.level = Logger::INFO
ActiveRecord::Base.establish_connection( :adapter  => 'sqlite3',
                                         :database => ':memory:' )

ActiveRecord::Schema.define do
  create_table "guests", :force => true do |t|
    t.string :name
  end

  create_table "reservations", :force => true do |t|
    t.references :guest
    t.date :check_in
  end
end

class Guest < ActiveRecord::Base
  has_many :reservations

  def self.by_first_check_in
    joins(:reservations).group("guests.id")
                        .select("guests.*")
                        .select("MIN(reservations.check_in) first_check_in")
                        .order("first_check_in")
  end

  def self.wrapped
    inner_scope = self.all # 3.2: use self.scoped
    self.unscoped do
      from(inner_scope.as(table_name))
    end
  end
end

class Reservation < ActiveRecord::Base
  belongs_to :guest
end

a = Guest.create(:name => 'A')
b = Guest.create(:name => 'B')

a.reservations.create(:check_in => Date.new(2012,7,23))
a.reservations.create(:check_in => Date.new(2012,8,24))

b.reservations.create(:check_in => Date.new(2012,7,21))
b.reservations.create(:check_in => Date.new(2012,8,22))

puts "#" * 80
puts <<-README

Active Record #{ActiveRecord::VERSION::STRING}
GOAL: get a count of rows from a query that includes pseudo columns and grouping.

When counting with grouping in the relation, ActiveRecord tries to return group
counts instead of a total count and drops the SELECT on the floor. If there are
pseudo-columns in the SELECT for grouping, this breaks the whole query.

Also, if it worked, this would return a hash instead of a fixnum. There doesn't
seem to be a way to get the total of returned rows, ignoring the grouping.

  * count should be usable to return a Fixnum count even when grouping is
    needed in the query.

  * relation.count and relation.all.count should have the same semantics.

  * count should work directly on any relation, so the relation can still be
    used as a chainable scope.

  * If I send the relation to the view, the paginator helper may call .count on
    it, so I have to use .all defensively in the right places. This conflicts
    with late-binding of things like table presenters that may want to chain
    additional scopes e.g. to change sorting. This is a conflict: paginator wants
    .count, table presenter wants a chainable scope.

Suggestion for Rails 4: The Calculations#count method is too ambiguous and assumes
too much. Change Calculations#count method to only return groups if :group option
is passed directly to the count method, i.e. do not trigger grouped counts based on
other group_values in the relation. That, or split #count and #count_grouped
into separate methods. This might make it easier to do the right thing with the
query builder.

README

relation = Guest.by_first_check_in

puts
puts "# relation.load"
result = relation.load
puts result.map{ |g| [g.name, g.first_check_in].join(" = ") }

puts
puts "# relation.to_a.count"
puts relation.to_a.count

puts
puts "# relation.load.count (expect: 2; throws an error)"
begin
  puts relation.load.count
rescue StandardError => e
  puts "fail: #{e}"
end

puts
puts "# relation.count (expect: 2; throws an error)"
begin
 puts relation.count
rescue StandardError => e
  puts "fail: #{e}"
end

puts
puts "#" * 80
puts <<-README

The above shows how regular count breaks. This is my workaround by hiding the
joins and groups in a subquery:

README
puts
puts "# relation.wrapped.load (in subquery)"
result = relation.wrapped.load
puts result.map{ |g| [g.name, g.first_check_in].join(" = ") }

puts
puts %{# relation.wrapped.where("first_check_in > '2012-07-21'").load}
result = relation.wrapped.where("first_check_in > '2012-07-21'").load
puts result.map{ |g| [g.name, g.first_check_in].join(" = ") }

puts
puts "# relation.wrapped.count (in subquery)"
puts relation.wrapped.count

puts
puts "# relation.wrapped.load.count"
puts relation.wrapped.load.count

puts
puts %{# relation.wrapped.where("first_check_in > '2012-07-21'").count}
puts relation.wrapped.where("first_check_in > '2012-07-21'").count

puts
puts "# relation.wrapped.group(:first_check_in).count"
puts relation.wrapped.group(:first_check_in).count

Unfortunately, we had a method that was being handed a Relation instance constructed with left outer joins in its query, and this technique did not handle that (Arel throws a NoMethodError on "left"). Someone else on my team discovered that ActiveRecord::Base has an undocumented #length method (which loads the relation under the hood -- syntactically a little nicer than having to explicitly call .all before .size or .count) that we're now using in the case where .count returns a Hash (which indicates that the relation was formed via GROUP BY).