Forum: Ruby on Rails extended count function for use with queries containing a GR

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
rails.list (Guest)
on 2005-12-01 19:15
(Received via mailing list)
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
This topic is locked and can not be replied to.