はじめに

1対多の関係を持ったテーブル同士をJOINしてGROUP BYして取得したときに、 多の方のデータをどうにかしてすべて取得したかったときの話です。

どんなテーブルだったか

わかりやすいように簡潔なテーブルにします。

userテーブル ユーザ情報のテーブル

mysql> SELECT * FROM user;
+----+------+
| id | name |
+----+------+
|  1 | hoge |
|  2 | fuga |
+----+------+

user_artist_relationテーブル ユーザとアーティストを紐付けるテーブル

mysql> SELECT * FROM user_artist_relation;
+---------+-----------+
| user_id | artist_id |
+---------+-----------+
|       1 |       101 |
|       1 |       102 |
|       1 |       103 |
|       2 |       102 |
|       2 |       104 |
+---------+-----------+

userテーブル対user_artist_relationテーブルが、1対多の関係になっています。

結合して取得

このときは、ユーザを基準にページネーションしなければならなかったので、 ユーザごとにグループ化して取得。

mysql> SELECT user.id, user.name, artist_id
    -> FROM user
    -> JOIN user_artist_relation ON user.id = user_artist_relation.user_id
    -> GROUP BY user.id;
+----+------+-----------+
| id | name | artist_id |
+----+------+-----------+
|  1 | hoge |       101 |
|  2 | fuga |       102 |
+----+------+-----------+

このようにしてしまうと当然ですが、先頭の artist_idしか取得できません。

また、MySQL 5.7.5からはSQLモードにデフォルトでONLY_FULL_GROUP_BYが設定されているのでエラーになります。

mysql> SELECT user.id, user.name, artist_id
    -> FROM user
    -> JOIN user_artist_relation ON user.id = user_artist_relation.user_id
    -> GROUP BY user.id;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.user_artist_relation.artist_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

ならばGROUP BYせずに以下のように全部取得したらいいのでは、、、

mysql> SELECT user.id, user.name, artist_id
    -> FROM user
    -> JOIN user_artist_relation ON user.id = user_artist_relation.user_id;
+----+------+-----------+
| id | name | artist_id |
+----+------+-----------+
|  1 | hoge |       101 |
|  1 | hoge |       102 |
|  1 | hoge |       103 |
|  2 | fuga |       102 |
|  2 | fuga |       104 |
+----+------+-----------+

しかしよくよく考えてみると、今回はユーザ基準でページネーションしなければならなかったので、当然全件数がおかしくなってしまいますし、LIMIT,OFFSETをつけると変なところで切れてしまうことに気づきました。orz そこでなんとかならないかと調べてみると、、、

GROUP_CONCAT関数

選ばれたのはGROUP_CONCAT関数でした。 GROUP BYしたときに、任意のカラムのデータを連結して文字列とすることで、データを1レコードにまとめることができます。 以下のようにして使います。

SELECT user.id, user.name,
       GROUP_CONCAT(artist_id) AS artist_ids
FROM user
JOIN user_artist_relation ON user.id = user_artist_relation.user_id
GROUP BY user.id;

このクエリを投げてみると

mysql> SELECT user.id, user.name,
    ->        GROUP_CONCAT(artist_id) AS artist_ids
    -> FROM user
    -> JOIN user_artist_relation ON user.id = user_artist_relation.user_id
    -> GROUP BY user.id;
+----+------+-------------+
| id | name | artist_ids  |
+----+------+-------------+
|  1 | hoge | 101,102,103 |
|  2 | fuga | 102,104     |
+----+------+-------------+

artist_idのすべてのデータが連結した形で取得することができました。 これで今回の問題を解決することができました。

他にも

今回はそんなに関係ないですがDISTINCTで重複データを削除できます。 また、連結する順序をORDER BYで指定できたり、区切り文字を指定できたりします。

mysql> SELECT user.id, user.name,
    ->        GROUP_CONCAT(DISTINCT artist_id ORDER BY artist_id DESC SEPARATOR '/') AS artist_ids
    -> FROM user
    -> JOIN user_artist_relation ON user.id = user_artist_relation.user_id
    -> GROUP BY user.id;
+----+------+-------------+
| id | name | artist_ids  |
+----+------+-------------+
|  1 | hoge | 103/102/101 |
|  2 | fuga | 104/102     |
+----+------+-------------+

まとめ

GROUP_CONCAT関数はとても便利でした。 但し、大規模データを扱う場合はSQLの実行計画を確認する必要があります。