CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
dcarneiro
dcarneiro

Reputation: 7150

rails 3 group by and sum

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

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)

Which returns an hash with activity_type_id => occurrences.

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

Upvotes: 13

Views: 26482

Answers (2)

Sandip Ransing
Sandip Ransing

Reputation: 7733

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.id and assuming activity_types_id is not needed as a part of hash key.

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 

Activity.joins(:activity_types).group(:activity_types_id, 'activity_types.name').order(:activity_types_id).sum(:occurrences)

Upvotes: 4

Marek Příhoda
Marek Příhoda

Reputation: 11198

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

Upvotes: 15

Related Questions