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}

Answer 2

If you're using Rails 4 or 5 you can do the following as well.

Tag.group(:id).count

Answer 3

The workaround for my situation seems to be to replace the :group => 'tags.id' with :select => 'DISTINCT tags.id' in the options hash before executing the count.

count_options = options.clone
count_options.delete(:order)

if options[:group]
  group_by = count_options[:group]
  count_options.delete(:group)
  count_options[:select] = "DISTINCT #{group_by}"
end

@item_count = @type.count(count_options)

Answer 4

Another (hacky) solution:

selection = Tag.where(...).group(...)
count = Tag.connection.select_value "select count(*) from (" + selection.to_sql + ") as x"

Answer 5

If I understand your question correctly, then it should work if you don't use Tag.count at all. Specifying 'COUNT(*) AS post_count' in your select hash should be enough. For example:

@tag = Tag.first(options)
@tag.post_count

As you can see, the post_count value from the query is accessible from the @tag instance. And if you want to get all tags, then perhaps something like this:

@tags = Tag.all(options)
@tags.each do |tag|
  puts "Tag name: #{tag.name} posts: #{tag.post_count}"
end

Update:

Count can be called with which attribute to count and also a parameter :distinct

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',
            :offset => (page - 1) * per_page,
            :limit => per_page }

@count = Tag.count(:id, :distinct => true, :joins => options[:joins])

@items = Tag.find(options)