I'm analyzing a few queries and have noticed some interesting results when trying to extract multiple fields from different tables based on a user_id. Consider the following tables:
+------------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+----------------+
| subscription_id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| user_id | int(11) unsigned | YES | MUL | NULL | |
| expires | int(11) unsigned | YES | MUL | NULL | |
| created | int(11) unsigned | YES | MUL | NULL | |
| modified | int(11) | YES | | NULL | |
+------------------------+------------------+------+-----+---------+----------------+
I want to GROUP_CONCAT all of a user's subscriptions when fetching users. Currently this method works for one field:
SELECT u.id,
(
SELECT GROUP_CONCAT(s.subscription_id)
FROM subscriptions s
WHERE s.user_id = u.id
) AS subscription_ids
FROM users u
LIMIT 10
And I can add all of the fields with different SELECT GROUP_CONCATs. However this method is very slow with any real number of rows due to MySQL joining the subscriptions table for every field. Is there a way that I can GROUP_CONCAT all of the fields at once when listing users?
I've tried a LEFT JOIN method:
SELECT
u.id AS user_id,
GROUP_CONCAT(s.subscription_id) AS subscription_ids
FROM users u
LEFT JOIN subscriptions s
ON s.user_id = u.id
LIMIT 10
However, that appears to concatenate all of the subscription_ids. I'm a bit stumped why the LEFT JOIN would do that. But I'm surprised there doesn't appear to be an easy way to concatenate a list of fields from a different table.
The GROUP_CONCAT is an aggregate function and therefore needs to be applied with GROUP BY clause.
SELECT
u.id AS user_id, GROUP_CONCAT(s.subscription_id) AS subscription_ids
FROM
users u
LEFT JOIN subscriptions s ON s.user_id = u.id
GROUP BY
u.id
LIMIT 10
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments