MySQL | GROUP_CONCAT() is my hero
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!