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().


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


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

It will show something like this

Project Members
Sakura Alice,Bob,Caroll
Ume Dean,Edison,Fox

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().


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

All Rights Reserved

Let's register a Viblo Account to get more interesting posts.