title: How to select join results into an array in mysql query?
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>For example, I would like the returned data to look like:</p>
  
  <pre><code>user_group | id | username | ...the rest
  1,3,5,7    | 12 | johndoe  | ...
  </code></pre>
  
  <p>here is the query I have.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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. </p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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. </p>