ActiveRecord’s regroup method is a shorthand for using unscope to remove existing groups.
Sometimes we want to change the query of an existing ActiveRecord::Relation object. Consider this query, which groups user’s by country:
1
query = User.group(:country)
We have a requirement to modify this query to group users by device type, in order to see what percentage use desktop vs mobile. The simplest way to solve the problem is to write a new query from scratch. However, if the query is complex and we only want to adjust the group, it’s easier to use regroup:
1
query.regroup(:device)
How does group work?
In the second half of this post we’ll explain how group works.
GROUP BY
group is an ActiveRecord method for writing SQL GROUP BY, which is used to group rows that have the same value. For example, this query groups results by country and counts the number of customers in each:
1
2
3
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country;
1
2
3
4
5
6
7
8
+-------------------+-----------+
| COUNT(CustomerID) | Country |
+-------------------+-----------+
| 3 | Argentina |
| 2 | Austria |
| 2 | Belgium |
| 9 | Brazil |
+-------------------+-----------+
ActiveRecord to SQL
Now that we understand how GROUP BY works. Let’s look at how group and regroup translate to SQL.
Here’s a query with a single group:
1
2
3
query = User.group(:country)
query.to_sql
=> "SELECT \"users\".* FROM \"users\" GROUP BY \"users\".\"country\""
It generates the following SQL:
1
2
3
SELECT users
FROM users
GROUP BY country
Let’s see what happens if we add a second group, without using regroup:
1
2
query.group(:device).to_sql
=> "SELECT \"users\".* FROM \"users\" GROUP BY \"users\".\"country\", \"users\".\"device\""
This results in the following SQL:
1
2
3
SELECT users
FROM users
GROUP BY country, device
This isn’t what we want. We don’t want to group by both country and device. We want the overall device breakdown. Let’s use regroup instead:
1
2
query.regroup(:device).to_sql
=> "SELECT \"users\".* FROM \"users\" GROUP BY \"users\".\"device\""
Now we have a SQL query which will correctly group by device:
1
2
3
SELECT users
FROM users
GROUP BY device