Double GROUP_CONCAT with MySQL 5.6

Question

I've got a query that bugs my mind right now.

Here's my basic query :

SELECT e.id AS `equipment.id`, t.id AS `task.id`, ttr.tags_id AS `tag.id`
FROM equipment e
  LEFT JOIN task t ON t.equipment = e.id
  INNER JOIN task_tag_relationship ttr ON ttr.task_id = t.id
WHERE e.id = 14;

And its result:

equipment.id  task.id   tag.id
14            10520     171
14            152040    171
14            223738    1
14            223738    4
14            331035    1
14            331035    4
14            1475152   1
14            1475152   4
14            1475152   5

What I'd like is to concat the tags.ids from each task with, let's say a ,, and then to concat these concatenated tags.ids with ;:

equipment.id  tags.ids
14            171;171;1,4;1,4;1,4,5

I've managed to do it with the following query:

SELECT GROUP_CONCAT(tags SEPARATOR ';') as `tags.ids`
FROM (
    SELECT GROUP_CONCAT(ttr.tags_id) as `tags`
    FROM task t
      INNER JOIN task_tag_relationship ttr ON ttr.task_id = t.id
    WHERE t.equipment = 14
    GROUP BY t.id
) as `task.tags`;

But the thing is that I cannot use that query into a query like this:

SELECT
  e.id, 
  SELECT GROUP_CONCAT(tags SEPARATOR ';') as `tags.ids`
  FROM (
    SELECT GROUP_CONCAT(ttr.tags_id) as `tags`
    FROM task t
      INNER JOIN task_tag_relationship ttr ON ttr.task_id = t.id
    WHERE t.equipment = e.id -- here's the problem, e.id is not available in the sub-subquery
    GROUP BY t.id
  ) as `task.tags`
FROM equipment e
WHERE e.id = 14

I've been trying to do it with JOINs and GROUP BYs but no better results.

I could obviously do it in code but I'm wondering if that's doable in MySQL.

Thanks in advance!

Lukas Eder · Answer 1 · 2020-04-11T15:07:39.823

0

You were close. Try this:

SELECT equipment, GROUP_CONCAT(tags SEPARATOR ';') as `tags.ids`
FROM (
  SELECT t.equipment, GROUP_CONCAT(ttr.tags_id) as `tags`
  FROM task t
    INNER JOIN task_tag_relationship ttr ON ttr.task_id = t.id
  GROUP BY t.equipment, t.id
) t
WHERE equipment = 14
GROUP BY equipment;

edited Apr 11 '20 at 15:07

answered Apr 11 '20 at 14:48

Lukas Eder

211,314
129
689
1,509

I've had to change `e.id` references to `t.equipment` to your query because the equipment table is not joined in the subquery. But the problem is still the same. I need to list all equipments and get their tags ids in this format. So what I wanna do is to replace the `14` id in the subquery by the equipment id of the main query. – Vincent Le Jeune Apr 11 '20 at 14:57
@VincentLeJeune: You can obviously filter the data from the outer query as well, see my updated answer. In your own attempt, you didn't project that column from the subquery (you didn't place it in `SELECT`), so you couldn't filter it. – Lukas Eder Apr 11 '20 at 15:02
```SELECT equipment_id, GROUP_CONCAT(tags SEPARATOR ';') as `tags.ids` FROM ( SELECT t.equipment equipment_id, GROUP_CONCAT(ttr.tags_id) as `tags` FROM task t INNER JOIN task_tag_relationship ttr ON ttr.task_id = t.id WHERE t.equipment = 14 GROUP BY ttr.task_id ) as `task.tags` WHERE equipment_id = 14;``` if I don't filter with a WHERE inside the subquery then it's super (super) slow cause it's bringing all the tasks and their tags. And if I do filter with a where I'm confronted to the same issue. – Vincent Le Jeune Apr 11 '20 at 15:15
@VincentLeJeune: MySQL's optimiser historically had problems pushing down predicates back into derived tables, yes. But I fail to understand what problem you're solving here, eventually. Just revert to your original query, then. Why does it not work for you? – Lukas Eder Apr 11 '20 at 15:20
Its just that I've been doing my whole side-project code with single queries, like getting all the equipments and all the necessary related data would have been cool, but I'll do it with multiple queries :) thanks for the help! – Vincent Le Jeune Apr 11 '20 at 15:44

Double GROUP_CONCAT with MySQL 5.6

1 Answers1