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