Unsafe parent/child cleanup
e.g. If a group has parent 1 and parent 21 and parent 31, then you delete 1, it will remove the other parents:
Original parents 1,21,31
would match 1,%
and would get replaced leaving 231
!
I think the better way to do this might be to do it in one query like:
// Ensure we're dealing with an SQL safe value:
$idBeingDeleted = (int) $dao->entity_id;
$dao = CRM_Core_DAO::execute(<<<SQL
UPDATE civicrm_group
WHERE parents RLIKE '\\b{$idBeingDeleted}\\b`
SET parents = TRIM(BOTH ',' FROM
REPLACE(
CONCAT_WS('', ',', parents, ','),
CONCAT(',', $idBeingDeleted, ','),
','
))
;
SQL
);
Could benchmark RLIKE against comparing to a book-ended expression CONCAT_WS('', ',',parents, ',') LIKE '%,{$idBeingDeleted},%
.
To show this produces expected results:
select trim(both ',' from replace(concat_Ws('', ',', '1,21,31', ','), concat(',', '1', ','), ',')) as removed1,
trim(both ',' from replace(concat_Ws('', ',', '1,21,31', ','), concat(',', '21', ','), ',')) as removed21,
trim(both ',' from replace(concat_Ws('', ',', '1,21,31', ','), concat(',', '31', ','), ',')) as removed31,
trim(both ',' from replace(concat_Ws('', ',', '1,21,31', ','), concat(',', '2', ','), ',')) as removed2,
trim(both ',' from replace(concat_Ws('', ',', '1,21,31', ','), concat(',', '3', ','), ',')) as removed3,
trim(both ',' from replace(concat_Ws('', ',', '1,21,31', ','), concat(',', '5', ','), ',')) as removed5
;
+----------+-----------+-----------+----------+----------+----------+
| removed1 | removed21 | removed31 | removed2 | removed3 | removed5 |
+----------+-----------+-----------+----------+----------+----------+
| 21,31 | 1,31 | 1,21 | 1,21,31 | 1,21,31 | 1,21,31 |
+----------+-----------+-----------+----------+----------+----------+
The children clean up code is the same.
Edited by Rich