ActiveRecord Count to count rows returned by group by in Rails

Question

I looked around and couldn't find any answers to this. All answers involved counts that did not use a GROUP BY.

Background: I have a paginator that will take options for an ActiveRecord.find. It adds a :limit and :offset option and performs the query. What I also need to do is count the total number of records (less the limit), but sometimes the query contains a :group option and ActiveRecord.count tries to return all rows returned by the GROUP BY along with each of their counts. I'm doing this in Rails 2.3.5.

What I want is for ActiveRecord.count to return the number of rows returned by the GROUP BY.

Here is some sample code that demonstrates one instance of this (used for finding all tags and ordering them by the number of posts with that tag):

options = { :select => 'tags.*, COUNT(*) AS post_count',
            :joins => 'INNER JOIN posts_tags',   #Join table for 'posts' and 'tags'
            :group => 'tags.id',
            :order => 'post_count DESC' }

@count = Tag.count(options)

options = options.merge { :offset => (page - 1) * per_page, :limit => per_page }

@items = Tag.find(options)

With the :select option, the Tag.count generates the following SQL:

SELECT count(tags.*, COUNT(*) AS post_count) AS count_tags_all_count_all_as_post_count, tags.id AS tags_id FROM `tags`  INNER JOIN posts_tags  GROUP BY tags.id  ORDER BY COUNT(*) DESC

As you can see it merely wrapped a COUNT() around the 'tags.*, COUNT(*)', and MySQL complains about the COUNT within a COUNT.

Without the :select option, it generates this SQL:

SELECT count(*) AS count_all, tags.id AS tags_id FROM `tags` INNER JOIN posts_tags GROUP BY tags.id ORDER BY COUNT(*)

which returns the whole GROUP BY result set and not the number of rows.

Is there a way around this or will I have to hack up the paginator to account for queries with GROUP BYs (and how would I go about doing that)?

Answer 1

Seems like you'd need to handle the grouped queries separately. Doing a count without a group returns an integer, while counting with a group returns a hash:

Tag.count
  SQL (0.2ms)  SELECT COUNT(*) FROM "tags"
 => 37

Tag.count(:group=>"tags.id")
  SQL (0.2ms)  SELECT COUNT(*) AS count_all, tags.id AS tags_id FROM "tags" 
    GROUP BY tags.id
 => {1=>37}