MySQL | GROUP_CONCAT() is my hero

Joeri Poesen //

Holy aggregate functions, batman! Why did nobody tell me there’s a function called GROUP_CONCAT() that does exactly what I’ve been dreaming of the past couple of days? RTFM, I guess. Oh well.

GROUP_CONCAT() returns a concatenated string of group values. This is super cool because it allows you to get a result like this:

1 | Alice | English, French, Italian

2 | Bob | French, German

...

retrieved from tables “people”, “languages” and languages_people” , with languages and people obviously having an n to n relationship through “languages_people”

Info and examples: MySQL – GROUP BY reference

practical example:

So, a practical example of this function kicking ass, is the following scenario:

I need to import content from a legacy CMS into drupal and correctly map the categorization scheme to taxonomy vocabularies and terms.

At first glance, the legacy CMS has a pretty clean data architecture. However, upon digging deeper, some it turns out the data model is not so straightforward as it seemed:

  • all article information is stored in table “articles” except the actual article content

  • actual article content is stored in table “pages” which has a foreign key to articles

  • articles can have more than one page, in which case each page is a separate record in the pages table (this made me cry)

That last bit of information means that the structure of the article is represented in the database, which is a big no-no in my opinion. It also means that I need to somehow merge all the pages together into one field before I can import the content into drupal.

Solution: I use group_concat() to concatenate all the page bodies into one field with a single query and then proceed with the import using the sweet node_import module . Cake!