Understanding How group Works Through ActiveRecord

This article was automatically translated from Japanese by AI. Read the original Japanese version →

Table of Contents

In this article, I'll explain how ActiveRecord's group method and related methods work, while also touching on how ActiveRecord's aggregate functions can be a little quirky.

Creating the Table

First, let's create a table like this.

exam_scores

  • subject : String
  • name : String
  • score : Integer
bundle exec rails g model exam_scores subject:string name:string score:integer

Creating the Data

Let's populate the table with the following data:

subject name score
Japanese Tanaka 50
Social Studies Tanaka 70
Social Studies Yamada 30
scores = [
  { subject: '国語', name: '田中', score: '50' },
  { subject: '社会', name: '田中', score: '70' },
  { subject: '社会', name: '山田', score: '30' }
]
ExamScore.create! scores

The Methods

Next, let's list the methods used for aggregation operations:

  • Specifying how to form groups
    • group
  • Aggregation
    • sum
    • average
    • count
  • Filtering aggregated results
    • having

group + sum

First, let's try using group and sum.

subject name score
Japanese Tanaka 50
Social Studies Tanaka 70
Social Studies Yamada 30
ExamScore.group(:subject).sum(:score)
=> {"国語"=>50, "社会"=>100}

We got the total score for each subject. Notice, though, that the result is a Hash rather than instances of ExamScore.

Let's look at the generated SQL.

SELECT SUM("exam_scores"."score") AS sum_score, subject AS subject FROM "exam_scores" GROUP BY "exam_scores"."subject"
-- 50|国語
-- 100|社会

The SQL result doesn't include name. That's because when you group the exam_scores table by subject, there isn't a single unique name for each group.

ActiveRecord is designed around the principles:

  • 1 table <=> 1 class
  • 1 record <=> 1 instance

This comes from the well-known object-oriented design pattern called the ActiveRecord pattern. The result of a SQL query using group + sum doesn't correspond to a single record in that table. Therefore, ExamScore.group(:subject).sum(:score) returns a plain Hash instead of ExamScore instances.

group + average + having

Next, let's switch subject to name and sum to average.

subject name score
Japanese Tanaka 50
Social Studies Tanaka 70
Social Studies Yamada 30
ExamScore.group(:name).average(:score)
=> {"山田"=>30, "田中"=>60}
# 実際は数字の部分はBigDecimal型でもどってきますが今回は省略

Now let's try using having.

ExamScore.group(:name).having('avg(score) > 50').average(:score)
=> {"田中"=>60}

Yamada disappeared (^-^)
Let's check the SQL.

SELECT AVG("exam_scores"."score") AS average_score, name AS name FROM "exam_scores" GROUP BY "exam_scores"."name" HAVING avg(score) > 50
-- 60.0|田中

Just like before, the result is a Hash, and there are two things to watch out for here.
First, the having method inserts its argument directly into the SQL as a raw string.

For example, if you change avg(score) to average_score:

ExamScore.group(:name).having('average_score > 50').average(:score)
=> {"田中"=>60}

Then in the generated SQL, avg(score) also becomes average_score:

SELECT AVG("exam_scores"."score") AS average_score, name AS name FROM "exam_scores" GROUP BY "exam_scores"."name" HAVING average_score > 50
-- 60.0|田中

That's what it outputs.

Second, having must be placed before the aggregate method (sum, average, count). That's because as soon as the aggregate method runs, it returns a Hash.

Let's try putting having at the end of the Ruby code from earlier.

ExamScore.group(:name).average(:score).having('average_score > 50')

The execution result looks like this:

NoMethodError: undefined method `having' for #<Hash:0x007f5c837ec778>
    from (irb):11
    from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands/console.rb:110:in `start'
    from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands/console.rb:9:in `start'
    from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands/commands_tasks.rb:68:in `console'
    from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands/commands_tasks.rb:39:in `run_command!'
    from /share/git_dir/simple_app/vendor/bundle/ruby/2.3.0/gems/railties-4.2.3/lib/rails/commands.rb:17:in `<top (required)>'
    from bin/rails:8:in `require'
    from bin/rails:8:in `<main>'

¯\_(ツ)_/¯

Summary

Aggregate functions sit somewhat outside ActiveRecord's core principles, so they can feel unintuitive if you first learned databases through ActiveRecord, and the related methods definitely have their quirks. Still, aggregate functions are one of the real joys of SQL, so it's well worth getting comfortable with them.