Using MySQL group_concat function with 'union' and 'in' in where clause

Question

i have a problem with correct execution of group_concat() function in MySQL. I have entities in my database which are subjects to actions. Certain actions require processing details, which are provided as an id of a processing. My processing details could be a child of some major processing and in db this is represented as

parent_id

In my query I'm asking database for all entities which have been processed by all child processings with the same parent_id as the processing of my starting entity (I get the single entity with all the info beforehand).

In database, the where clause looks like this:

where p.processing_id in
    (SELECT processing_id FROM processing_type_1 where parent_id in 
        (select parent_id from processing_type_1 where parent_id in (p.processing_id)))
        union all (SELECT processing_id FROM processing_type_2 where parent_id = 
            (select parent_id from processing_type_2 where parent_id in (p.processing_id)))

This clause works as intended until I try to add a group_concat() in the select clause. Each action on entity has personnel assigned to it and I need to get all people assigned to certain actions.

The select clause looks like this:

group_concat(distinct (select z.full_name from user z join entity_action ea on z.id = ea.personnel_id where ea.entity_id = e.id and ea.action_type = 'some_action' order by z.full_name) separator ', ') as action_personnel

This said query is not working until I add

limit 1

at the end of the subquery in group_concat(). It wouldn't be a problem if there was only one person assigned to the action but unfortunately, this is not the case.

My question is - is there any way to make these two work?

My example data looks like that:

entity_table

entity_id
1
2
3

entity_action table

action_id|entity_id|  action_name|personnel_id|processing_id
        1|        1|'some action'|           1|           15
        2|        1|      'other'|           1|
        3|        1|    'another'|           2|
        4|        1|'some action'|           3|           17

processing table

processing_id|parent_id
           15|        5
           17|        5

If I asked about all processings with parent_id of 5 my desired result in this case would be

entity_id|action_personnel
        1|             1,3

My full query is as follows:

SELECT e.entity_id, 
group_concat(distinct (select z.full_name from user z join entity_action ea on z.id = ea.personnel_id where ea.entity_id = e.id and ea.action_type = 'some_action' order by z.full_name) separator ', ') as action_personnel
FROM enity e 
inner join entity_action ea on ea.entity_id = e.entity_id 
left outer join processing p on p.id = ea.entity_id
where
(1 IS null OR 
p.processing_id in
(SELECT processing_id FROM processing_type_1 where parent_id in 
    (select parent_id from processing_type_1 where parent_id in (p.processing_id)))
    union all (SELECT processing_id FROM processing_type_2 where parent_id = 
        (select parent_id from processing_type_2 where parent_id in (p.processing_id)))
)
group by e.entity_id;

Answer 1

Had more of a look at this now.

I think you basic query can eliminate some of the IN clauses like this (but this WILL NOT work)

SELECT e.entity_id, 
        GROUP_CONCAT((SELECT DISTINCT z.full_name FROM user z INNER JOIN entity_action ea ON z.id = ea.personnel_id WHERE ea.entity_id = e.id AND ea.action_type = 'some_action') ORDER BY full_name SEPARATOR ', ') AS action_personnel
FROM enity e 
INNER JOIN entity_action ea ON ea.entity_id = e.entity_id 
WHERE (1 IS NULL 
OR p.processing_id in
(
    SELECT processing_id 
    FROM processing_type_1 pt1_a
    INNER JOIN processing_type_1 pt1_b
    ON pt1_a.parent_id = pt1_b.parent_id 
    INNER processing p on p.id = ea.entity_id
    ON pt1_b.parent_id = p.processing_id
    UNION  
    SELECT processing_id pt2_a
    FROM processing_type_2 
    INNER JOIN processing_type_2 pt2_b
    ON pt2_a.parent_id = pt2_b.parent_id 
    INNER processing p on p.id = ea.entity_id
    ON pt2_b.parent_id = p.processing_id
)
)
GROUP BY e.entity_id;

Looking at this you have a sub query in the SELECT within a GROUP_CONCAT. Never seen anyone try this before, and the manual doesn't mention anything about this in a GROUP CONCAT. However when I play with such syntax it appears that MySQL is confused as it has a sub query returning multiple rows within the SELECT, and this causes an error. This is why it works when you add a LIMIT 1 to the sub query, as it forces it to return a single row.

My cleaned up query suffers the same.

It may be possible to clean this up to just do a join on user and entity action in the main query, but not certain with your data.

What you can do is join against a sub query to get all the full names grouped together for each entity_id:-

SELECT e.entity_id, 
        sub1.action_personnel
FROM enity e 
INNER JOIN entity_action ea ON ea.entity_id = e.entity_id 
INNER JOIN 
(
    SELECT ea.entity_id,
            GROUP_CONCAT(DISTINCT z.full_name ORDER BY full_name SEPARATOR ', ') AS action_personnel
    FROM user z 
    INNER JOIN entity_action ea 
    ON z.id = ea.personnel_id 
    WHERE ea.entity_id = e.id 
    AND ea.action_type = 'some_action'
    GROUP BY ea.entity_id
) sub1
ON sub1.entity_id = e.id 
WHERE (1 IS NULL 
OR p.processing_id in
(
    SELECT processing_id 
    FROM processing_type_1 pt1_a
    INNER JOIN processing_type_1 pt1_b
    ON pt1_a.parent_id = pt1_b.parent_id 
    INNER processing p on p.id = ea.entity_id
    ON pt1_b.parent_id = p.processing_id
    UNION  
    SELECT processing_id pt2_a
    FROM processing_type_2 
    INNER JOIN processing_type_2 pt2_b
    ON pt2_a.parent_id = pt2_b.parent_id 
    INNER processing p on p.id = ea.entity_id
    ON pt2_b.parent_id = p.processing_id
)
)
GROUP BY e.entity_id;

Answer 2

Try following query :

SELECT e.entity_id, 
GROUP_CONCAT(DISTINCT z.full_name ORDER BY z.full_name) SEPARATOR ', ') AS action_personnel
FROM enity e 
INNER JOIN entity_action ea ON ea.entity_id = e.entity_id 
LEFT OUTER JOIN processing p ON p.id = ea.entity_id
LEFT JOIN `user` z ON z.id = ea.personnel_id AND ea.action_type = 'some_action' 
WHERE
(1 IS NULL OR 
p.processing_id IN
(SELECT processing_id FROM processing_type_1 WHERE parent_id IN 
    (SELECT parent_id FROM processing_type_1 WHERE parent_id IN (p.processing_id)))
    UNION ALL (SELECT processing_id FROM processing_type_2 WHERE parent_id = 
        (SELECT parent_id FROM processing_type_2 WHERE parent_id IN (p.processing_id)))
)
GROUP BY e.entity_id;