Multiple GROUP_CONCAT on different fields using MySQL

Question

I have a query like this:

SELECT product.id,
       GROUP_CONCAT(image.id) AS images_id,
       GROUP_CONCAT(image.title) AS images_title,
       GROUP_CONCAT(facet.id) AS facets_id
...
GROUP BY product.id

And the query works, but not as expected, because if I have a product with 5 facets and 1 image (suppose an image with id=7), then I get something like this in "images_id":
"7,7,7,7,7"
If I have 2 images (7 and 3) then I get something like:
"7,7,7,7,7,3,3,3,3,3"
and in facets I get something like:
"8,7,6,5,4,8,7,6,5,4"

I think MySQL is making some type of union of the differents rows returned by the query, and then concatenating everything.

My expected result is (for the last example):

images_id = "7,3"
facets_id = "8,7,6,5,4"

I can obtain that using DISTINCT in the GROUP_CONCAT, but then I have another problem: If I have two images with the same title, one of them is ommited, and then I get something like:
images_id = "7,3,5"
images_title = "Title7and3,Title5"

So I miss the relation between images_id and images_title.

Does somebody know if it's possible to make this query in MySQL?

Maybe I'm complicating everything without any real benefits. I'm trying to execute only one query because performance, but now I'm not so sure if it's even faster to execute two queries (one for selecting the facets and another for the images for example).

Please explain what do you think is the best solution for this and why.

Thanks !

Answer 1

Just add DISTINCT.

Example:
GROUP_CONCAT(DISTINCT image.id) AS images_id

Answer 2

You'll need to get each group separately:

SELECT
    p.id,
    images_id,
    images_title,
    facets_id,
    ...
FROM PRODUCT p
JOIN (SELECT product.id, GROUP_CONCAT(image.id) AS images_id
      FROM PRODUCT GROUP BY product.id) a on a.id = p.id
JOIN (SELECT product.id, GROUP_CONCAT(image.title) AS images_title
      FROM PRODUCT GROUP BY product.id) b on b.id = p.id
JOIN (SELECT product.id, GROUP_CONCAT(facet.id) AS facets_id
      FROM PRODUCT GROUP BY product.id) b on c.id = p.id
...