This post hasn't been updated for 8 years
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() 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| 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 by
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 is sometimes useful for optimizing a query for has_many relation.
All Rights Reserved