ondrejd's blog

Today I discovered and firstly used MySQL aggregate function GROUP_CONCAT and it’s really usefull in same cases. Consider structure database like this:

┌───────────────────────────────┐    ┌───────────────────────┐
│         eshop_items           │    │ eshop_items_to_genres │
├───────────────────────────────┤    ├───────────────────────┤
│ * ID                [INTEGER] |----|   item_ID   [INTEGER] │
│   title             [VARCHAR] │    │   genre_ID  [INTEGER] |----┐
│   image             [VARCHAR] │    └───────────────────────┘    |
│   director          [VARCHAR] │                                 |
│   actor             [VARCHAR] │                                 |
│   language          [VARCHAR] │    ┌─────────────────────────┐  |
│   subtitles         [VARCHAR] │    │ items_prize_categories  │  |
│   custom            [VARCHAR] │    ├─────────────────────────┤  |
│   prize_category_ID [INTEGER] |----| * ID          [INTEGER] │  |
│   created         [TIMESTAMP] │    │   title       [VARCHAR] │  |
│   display          [SHORTINT] │    │   prize       [INTEGER] │  |
└───────────────────────────────┘    └─────────────────────────┘  |
                              ┌────────────────────────────────┐  |
                              │     items_genre_categories     │  |
                              ├────────────────────────────────┤  |
                           ┌--| + ID                 [INTEGER] |--┘
                           └--|   parent_category_ID [INTEGER] │
                              │   title              [VARCHAR] │
                              └────────────────────────────────┘

And I was asked for creating SELECT query which returns data in table like this:

┌─────────┬────────────┬─────────────────────┬────────────────┐
│ Item ID │ Item title │ All attached genres │ Prize category │
├─────────┼────────────┼─────────────────────┼────────────────┤
│ 1       │ Any movie  │ Drama,Crime,Foreign │ Cheapest       │
│ ...     │ ...        │ ...                 │ ...            │

So after a while I created this query:

SELECT
  `t2`.`ID` AS `item_ID`,
  `t2`.`title` AS `item_title`,
  GROUP_CONCAT(`t3`.`title` ORDER BY `t3`.`title` ASC SEPARATOR ', ') AS `genre_categories`,
  `t4`.`title` AS `prize_category`
FROM `eshop_items_to_genres` AS `t1`
JOIN `eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`item_ID`
JOIN `items_genre_categories` AS `t3` ON `t3`.`ID` = `t1`.`genre_category_ID`
JOIN `items_prize_categories` AS `t4` ON `t4`.`ID` = `t2`.`prize_category_ID`
WHERE
  `t2`.`display` = 1
GROUP BY `item_title`
ORDER BY `item_title` ASC

Before only a few years I certainly did this only using more SELECT queries so I’m a little bit proud of myself .