Ruby-on-rails ActiveRecord Count 计算 Rails 中 group by 返回的行数

ActiveRecord Count to count rows returned by group by in Rails

提问by J.Melo

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

我环顾四周，找不到任何答案。所有答案都涉及未使用 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.

背景：我有一个分页器，可以为 ActiveRecord.find 选择选项。它添加了一个 :limit 和 :offset 选项并执行查询。我还需要做的是计算记录总数（减去限制），但有时查询包含一个 :group 选项，ActiveRecord.count 尝试返回 GROUP BY 返回的所有行及其每个计数。我在 Rails 2.3.5 中这样做。

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

我想要的是 ActiveRecord.count 返回 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 选项，Tag.count 生成以下 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.

正如您所看到的，它只是在“tags.*, COUNT(*)”周围包裹了一个 COUNT()，而 MySQL 会抱怨 COUNT 中的 COUNT。

Without the :select option, it generates this SQL:

如果没有 :select 选项，它会生成此 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.

它返回整个 GROUP BY 结果集而不是行数。

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)?

有没有办法解决这个问题，或者我是否必须修改分页器来处理 GROUP BY 的查询（我将如何去做）？

采纳答案by J.Melo

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.

我的情况的解决方法似乎是在执行计数之前将选项哈希中的 :group => 'tags.id' 替换为 :select => 'DISTINCT tags.id' 。

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)

回答by zetetic

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}

回答by Darwayne

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

如果您使用的是 Rails 4 或 5，您也可以执行以下操作。

Tag.group(:id).count

回答by troelskn

Another (hacky) solution:

另一个（hacky）解决方案：

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

回答by DanneManne

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.count，它应该可以工作。在您的选择哈希中指定 'COUNT(*) AS post_count' 就足够了。例如：

@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:

如您所见，查询中的 post_count 值可从 @tag 实例访问。如果你想获得所有标签，那么可能是这样的：

@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

可以使用要计数的属性和参数调用计数：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)