group_concat()

Background

One of the common problem with RDBMS is that it can not handle well has-many relation by a single query. As ActiveRecord's eage load does, we need two separate queries to load parent and child tables. I believed this as a matter of fact until I know about group_concat().

Solution

group_concat() is a MySQL's embedded function that allows us to load associated has-many data as a single column with GROUP BY clause. Let's look at an example.

Think about the following two tables.

CREATE TABLE projects (
id int not null auto_increment,
name varchar(255),
primary key(id)
);

CREATE TABLE members (
id int not null auto_increment,
name varchar(255),
is_leader boolean,
project_id int not null,
primary key(id)
);

Obviously a project has many members. We want to implement a page which shows a table containing

• project name
• each member's name separated by comma, and leaders's name comes first.

If you implement this naively, it would look like this:

@projects = Project.preload(:members).to_a

view

- @projects.each do |project|
  tr
    td= project.name
    td= project.members.sort_by(&:is_leader).map(&:name).join(",")

It will show something like this

But sometimes we want to avoid loading has-many association and building many models, because as we know building ActiveRecord instances are one of the most costly operation in RoR.

We can solve this problem bygroup_concat().

SELECT p.name,
       group_concat(m.name ORDER BY m.is_leader DESC) AS members
  FROM projects p
       INNER JOIN members m
          ON m.project_id = p.id
 GROUP BY p.id

Then it will show a result like this.

+--------+------------------+
| name   | members          |
+--------+------------------+
| Sakura | Alice,Bob,Caroll |
| Ume    | Dean,Edison,Fox  |
+--------+------------------+

So we can load desired data by a single query. Notice that we can use ORDER BY in group_concat().

Conclusion

group_concat is sometimes useful for optimizing a query for has_many relation.