Categories
Databases Postgresql

Magic of Mysql’s GROUP_CONCAT()

I lived for a long time without knowing about this function, but it is really great when you need to create quick reports.

E.g. imagine that you have three tables:

Users table:

+----+------------------+
| id | email            |
+----+------------------+
|  1 | first@email.com  |
|  2 | second@email.com |
|  3 | third@email.com  |
+----+------------------+

Clubs table:

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Musicians   |
|  2 | Artists     |
|  3 | Programmers |
+----+-------------+

And a many-to-many relationship table:

+---------+----------+
| user_id | club_id |
+---------+----------+
|       1 |        1 |
|       1 |        2 |
|       2 |        2 |
|       2 |        3 |
|       3 |        1 |
|       3 |        2 |
|       3 |        3 |
+---------+----------+

What if you needed to see which user belongs to which group, in a human readable format? You could write a query like:

SELECT user.email, club.name AS club_name 
FROM user 
INNER JOIN user_club ON (user.id = user_club.user_id) 
INNER JOIN club ON (club.id = user_club.club_id)

But the result will be messy (though easy to parse for a program afterwards):

+------------------+-------------+
| email            | club_name   |
+------------------+-------------+
| first@email.com  | Musicians   |
| third@email.com  | Musicians   |
| first@email.com  | Artists     |
| second@email.com | Artists     |
| third@email.com  | Artists     |
| second@email.com | Programmers |
| third@email.com  | Programmers |
+------------------+-------------+

If you want to have an instantly human-readable result, you can utilize GROUP_CONCAT() like this:

SELECT user.email, GROUP_CONCAT(club.name) AS clubs 
FROM user 
INNER JOIN user_club ON (user.id = user_club.user_id) 
INNER JOIN club ON (club.id = user_club.club_id) 
GROUP BY user.id
+------------------+-------------------------------+
| email            | clubs                         |
+------------------+-------------------------------+
| first@email.com  | Musicians,Artists             |
| second@email.com | Artists,Programmers           |
| third@email.com  | Musicians,Artists,Programmers |
+------------------+-------------------------------+

Much nicer, isn’t it? Beware, this is not standard SQL and it won’t work in e.g. PostgreSQL, but you can utilize something like this in it:

SELECT user.email, array_to_string(array_agg(club.name), ',') 
FROM user 
INNER JOIN user_club ON (user.id = user_club.user_id) I
NNER JOIN club ON (club.id = user_club.club_id) 
GROUP BY user.id