Ruby-on-rails rails 3 group by 和 sum
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9068926/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
rails 3 group by and sum
提问by dcarneiro
I have the following model:
我有以下模型：
activity_types: id, name
activities: id, id_activity_type, occurrences, date (other fields)
The activities table store how many times an activity occurs by day. But now I want to show to the user how many activities from each type occurred by month.
活动表存储活动每天发生的次数。但现在我想向用户显示每个月发生的每种类型的活动数量。
I got the following solution based on this postwhich seems ok:
我根据这篇文章得到了以下解决方案，看起来不错：
Activity.all(:joins => :activity_types,
:select => "activity_types.id, activity_types.name, SUM(activities.occurrences) as occurrences",
:group => "activity_types.id, activity_types.name",
:order => "activity_types.id")
but this seems a lot of code for the rails standards and rails API says it's deprecated.
但这似乎有很多用于 rails 标准的代码，而rails API 表示它已弃用。
I found the following solution which is a lot simple:
我发现以下解决方案非常简单：
Activity.sum(:occurrences).group(:activity_type_id)
Which returns an hash with activity_type_id=> occurrences.
它返回一个带有activity_type_id=> 出现的哈希值。
What shall I do to get the following hash: activity_type.name=> occurrences ?
我该怎么做才能获得以下哈希值：activity_type.name=> 出现次数？
回答by Marek P?íhoda
If the original query worked, then just try rewriting it with Rails 3 syntax:
如果原始查询有效，那么只需尝试使用 Rails 3 语法重写它：
Activity.joins(:activity_types)
.select("activity_types.id, activity_types.name, SUM(activities.occurrences) as occurrences")
.group("activity_types.id, activity_types.name")
.order("activity_types.id")
回答by Sandip Ransing
Activity.joins(:activity_types).group('activity_types.name').sum(:occurrences)
Activity.joins(:activity_types).group('activity_types.name').sum(:occurrences)
SELECT SUM(activities.occurrences) AS sum_occurrences, activity_types.name AS activity_types_name FROM activity_types INNER JOIN activity_types ON activity_types.id = activities.activity_types_id GROUP BY activity_types.name
in case you needed an ordered hash based on activity_types.idand assuming activity_types_idis not needed as a part of hash key.
如果您需要基于activity_types.id和假设的有序散列activity_types_id作为散列键的一部分。
Activity.joins(:activity_types).group('activity_types.name').order(:activity_types_id).sum(:occurrences)
incase [activity_type_id, activity_types.name] needed as a part of key
incase [activity_type_id, activity_types.name] 需要作为密钥的一部分
Activity.joins(:activity_types).group(:activity_types_id, 'activity_types.name').order(:activity_types_id).sum(:occurrences)
