How to select join results into an array in mysql query?

Question

I'm trying to write a script that will export modx users to CSV, fairly straightforward stuff, but in modx users can belong to many groups. Simply joining the modx_member_groups table will result in several rows for different users.

What I would like to do is somehow rewrite the query below so that the join on the modx_member_groups would return a list or array of group ids that the user belongs to.

For example, I would like the returned data to look like:

user_group | id | username | ...the rest
1,3,5,7    | 12 | johndoe  | ...

here is the query I have.

SELECT mg.user_group, u.id, u.username, ua.*

FROM modx_users u 

LEFT JOIN modx_user_attributes ua ON u.id = ua.internalKey 

LEFT JOIN modx_member_groups mg ON u.id = mg.member

LIMIT 10

Ideally it would be awesome to somehow select the actual group names as columns. and then just force a true or false in the group name column.

UPDATE

I've updated the query after shtever's answer but have performance issues: - GROUP_CONCAT was returning a BLOB type so I had to convert it, setting the group_concat_max_length to below 512 was not working

SELECT GROUP_CONCAT(CONVERT(mg.user_group, CHAR(10)) ORDER BY mg.user_group SEPARATOR ',') AS groups, u.id, u.username, ua.*
FROM modx_users u 
LEFT JOIN modx_user_attributes ua ON u.id = ua.internalKey 
LEFT JOIN modx_member_groups mg ON mg.member = u.id
GROUP BY u.id

The query now takes 27.5 seconds to execute if I limit it to 10 results or let it run on the entire 6000 users it always takes 27.5 seconds. If I remove the GROUP_CONCAT ~ same amount of time.

Is this a MySQL database?

schtever
– schtever

2015-01-25 02:09:37 +00:00
Commented Jan 25, 2015 at 2:09 — schtever
– schtever, Commented Jan 25, 2015 at 2:09

schtever · Accepted Answer · 2015-01-25 02:20:41Z

6

For MySQL, take a look at the GROUP_CONCAT function. Mysql GROUP_CONCAT Description

Your query might look something like:

SELECT GROUP_CONCAT(DISTINCT mg.user_group ASC SEPARATOR ',') , u.id, u.username, ua.*
FROM modx_users u 
LEFT JOIN modx_user_attributes ua ON u.id = ua.internalKey 
LEFT JOIN modx_member_groups mg ON u.id = mg.member
GROUP BY u.id, u.username
LIMIT 10

You might have to fiddle with the GROUP BY fields depending on the relation between the modx_user_attributes and modx_users table.

answered Jan 25, 2015 at 2:20

schtever

3,25019 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sean Kimball Over a year ago

Thanks - that does work, b ut I had to tweak it a little bit [see update] I don't think I need the DISTINCT keyword as a user cannot belong to the same group twice, and it was returning a BLOB type [even if I set the group_concat_max_length] so I had to convert it. However it is slow 27.5 seconds no matter what I do, with or without the CONVERT even if I remove the LIMIT, 10 records or 6000 records[there are 6000 users], it always takes 27.5 seconds!

Sean Kimball Over a year ago

indexing the member column in the modx_member_groups table solves the speed issue[queries under a second], but I'm not sure how that will affect the rest of the database?

schtever Over a year ago

Indexes occupy disk space and have to be maintained on inserts,updates,and deletes. In general, the overhead its worth it for the performance boost.

Collectives™ on Stack Overflow

How to select join results into an array in mysql query?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related