Mysql – the best way to handle a join / group_concat query in MySQL

concatgroup-concatenationjoin;MySQL

I am looking for some feedback regarding the best way to handle the following (simplified for this example) situation.

I have 3 tables: tasks, assignees, and users

---tasks----
id, task, completed

---assignees----
user_id, task_id

---users---
id, name

I need to select tasks with concatenated user names of assignees, like so

tasks.task  |   assigned_names.names   
---------------------------------------
"My task"   |   "Joe Schmoe, Bob Mcbart, Sally McGuire"

Here is what I have currently

SELECT tasks.task, assigned_names.names

FROM tasks

LEFT JOIN (
   SELECT 
      GROUP_CONCAT( users.name SEPARATOR ',' ) AS names,
      assignees.task_id
      FROM assignees
      LEFT JOIN users ON users.id = assignees.user_id
      GROUP BY assignees.task_id
   
) assigned_names ON assigned_names.task_id = tasks.id

WHERE tasks.completed IS NULL

My main concern is that the assignees table has hundreds of thousands of records, and doing a group on the whole table every time seems like not a good idea.

Is there a better strategy?

Also, I would be happy to rephrase my question title if someone could suggest better language.

Thanks!

Best Answer

Simplicity is beautify. Multiple JOINs are allowed. There is no need to the JOINs to be LEFT JOINS as you want data that maches always.

SELECT tasks.task, GROUP_CONCAT( users.name SEPARATOR ',' ) AS names,
FROM assignees
JOIN tasks ON assignees.task_id = tasks.id
JOIN users ON assignees.user_id = users.id
WHERE tasks.completed IS NULL
GROUP BY assignees.task_id

Related Solutions

Mysql – Join two group_concat result

You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation:

GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, rtg.id) 
              ORDER BY rtg.id
              SEPARATOR ','                      -- the comma ',' is the default
            ) AS groupList

MySQL GROUP_CONCAT – Fix Duplicate Data Issue When DISTINCT Can’t Be Used

You have identified the source of the problem: that recipe is joined to two tables, recipe_detail and recipe_tagmap (and these to several other tables related to respectively "ingredients" and "tags"), and recipe is having one-to-many relationships with both of them.

One solution is to individually GROUP BY and aggregate first (one aggregation for the list of the tables related to ingredients and another for the group of tables related to tags, and then join back (again) to the main table (recipe):

SELECT recipe.*, 
       iid,  
       iname, 
       mabbr, 
       tag
FROM  recipe

  LEFT JOIN 
    ( SELECT recipe_detail.recipe_id,
             GROUP_CONCAT(recipe_detail.ingredient_id) AS iid,  
             GROUP_CONCAT(ingredient.name) AS iname, 
             GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr
      FROM recipe
        JOIN recipe_detail
          ON recipe.id = recipe_detail.recipe_id
        LEFT JOIN ingredient
          ON recipe_detail.ingredient_id = ingredient.id
        LEFT JOIN ingredient_mfr
          ON ingredient.mfr_id = ingredient_mfr.id
      WHERE recipe.user_id = 1
      GROUP BY recipe_detail.recipe_id
    ) AS details
        ON recipe.id = details.recipe_id

  LEFT JOIN
    ( SELECT recipe_tagmap.recipe_id,
             GROUP_CONCAT(recipe_tag.name) AS tag 
      FROM recipe
        JOIN recipe_tagmap
          ON recipe.id = recipe_tagmap.recipe_id
        LEFT JOIN recipe_tag
         ON recipe_tagmap.tag_id = recipe_tag.id
      WHERE recipe.user_id = 1
      GROUP BY recipe_tagmap.recipe_id
    ) AS tags
      ON recipe.id = tags.recipe_id

WHERE recipe.user_id = 1 ;

Tested at: SQL-Fiddle

(Using the recipe table inside the 2 aggregations is not strictly needed but since you only want the recipes of one user, it will help for efficiency, restricting the number of rows retrieved from several tables and aggregated.)

Best Answer

Related Solutions

Mysql – Join two group_concat result

MySQL GROUP_CONCAT – Fix Duplicate Data Issue When DISTINCT Can’t Be Used

Related Question