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;
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;