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.