rails 3 group by 和 sum [英] rails 3 group by and sum

问题描述

我有以下模型:

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 post which 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")

但这似乎有很多用于 rails 标准的代码，rails API 表示它已弃用.

but this seems a lot of code for the rails standards and rails API says it's deprecated.

我找到了以下非常简单的解决方案:

I found the following solution which is a lot simple:

Activity.sum(:occurrences).group(:activity_type_id)

返回具有 activity_type_id => 次出现的哈希值.

Which returns an hash with activity_type_id => occurrences.

我该怎么做才能获得以下哈希值:activity_type.name => 出现次数?

What shall I do to get the following hash: activity_type.name => occurrences ?

推荐答案

如果原始查询有效，那么只需尝试使用 Rails 3 语法重写它:

If the original query worked, then just try rewriting it with Rails 3 syntax:

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

这篇关于rails 3 group by 和 sum的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！