Context:
Our database contains orphaned records (e.g., Tasks, Notes) where the parent_type and parent_id reference deleted parent records. These orphaned records, some over five years old, need cleanup to improve database efficiency.
Objective:
Convert the following MySQL queries into a SugarCRM function that adheres to its logic, framework, and ORM practices for managing data integrity.
Request:
Provide guidance or implementation steps for converting this functionality into a SugarCRM-compliant solution.
Fullscreen
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SET SESSION group_concat_max_len = 100000;
SELECT GROUP_CONCAT(
CONCAT(
"SELECT m.id, m.parent_type, m.parent_id FROM tasks m ",
"LEFT JOIN ", lower(parent_type), " jt ON m.parent_id = jt.id ",
"WHERE m.deleted = 0 AND m.parent_type = '", parent_type, "' AND jt.id IS NULL"
)
SEPARATOR " UNION ALL "
)
INTO @dynamic_query
FROM (SELECT DISTINCT parent_type FROM tasks) t;
PREPARE stmt FROM @dynamic_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;