Rails - Dec 2005 - [MySQL] extended count function for use with queries containing a GROUP BY clause

Hi everybody.

While playing around with pagination I noticed that when using certain 
GROUP BY clauses Rails would think that your result contains less lines 
than it actually does. This is related to the use of a plain "SELECT 
COUNT(*)" for getting the number of total rows which the following query 
will return.

In order to bypass this problem, I created the following replacement for 
ActiveRecord::Base.count:
module ActiveRecord
   class Base
      class << self
         def count(conditions = nil, joins = nil)
         if joins =~ /GROUP BY/
            sql = "SELECT SQL_CALC_FOUND_ROWS * FROM #{table_name} "
            sql += joins if joins
            sql += '' LIMIT 0''
            add_conditions!(sql, conditions)
            connection.execute(sql)
            sql = ''SELECT FOUND_ROWS()''
         else
            sql = "SELECT COUNT(*) FROM #{table_name} "
            sql << " #{joins} " if joins
            sql << ''LIMIT 1''
            add_conditions!(sql, conditions)
         end
         count_by_sql(sql)
         end
      end
   end
end

As you can see it makes use of the new SQL_CALC_FOUND_ROWS introduced in 
MySQL 4.0. I didn''t upload this as a regular patch to Rails as I guess 
it''s MySQL-specific. Also it doesn''t seem like the most
elegant thing to
do as most likely there will be some overhead in executing the query 
twice even if there won''t be any rows returned the first time.

I had to do this as we have to know how many rows there are in total in 
order to create a paginator. At least I pretty much think so as 
currently it is implemented this way ;)

Is anybody aware of a better™ way to do this or similar implementations 
for other RDBMs?

Regards,
Niels