MySQL Group_Concat through a Join

So today I came across a problem where I want to pull all available projects in the system and also get a list of the users associated with that project. The way we had done it before was to grab a list of the projects and do an additional call for each to find the users. That turns out to be kind of a pain, especially on processing time. Today I taught myself how to use the GROUP_CONCAT feature and turn about 10 seconds of compute time down to about 0.0468 seconds. Gnarly! Here’s the sql:

SELECT CONCAT_WS( ' ', u.firstName, u.lastName ) ,
(
SELECT GROUP_CONCAT( DISTINCT userId ) AS user_ids
FROM projectsharing ps
WHERE ps.projectId = project.id
GROUP BY ps.projectId
) AS combinedusers, project.name, project.description, project.estimatedHours, project.actualHours, project.ownerId, project.parentId, project.id
FROM project
LEFT OUTER JOIN user u ON ( u.id = project.ownerId )

This outputs our project data with a list of concatenated user ids.

Comments are closed.