Untitled
user_2735721
sql
a year ago
3.0 kB
4
Indexable
SELECT r.id FROM responsibilities r INNER JOIN responsibility_assignees ra ON r.id = ra.responsibility_id JOIN responsibility_assignee_due_dates radd ON radd.resp_id = r.id AND( ( r.assignee_type = 'all' AND r.rc_type = 'all' AND ra.assignee_id = radd.assignee_id AND ra.responsibility_center_id = radd.responsibility_center_id ) OR( r.assignee_type = 'all' AND( r.rc_type = 'any' OR r.rc_type IS NULL ) AND ra.assignee_id = radd.assignee_id ) OR( r.assignee_type = 'any' AND r.rc_type = 'all' AND ra.responsibility_center_id = radd.responsibility_center_id ) OR( r.assignee_type = 'any' AND( r.rc_type = 'any' OR r.rc_type IS NULL ) ) ) LEFT JOIN responsibility_assignee_delegations rad ON r.id = rad.resp_id and rad.status = 'active' LEFT JOIN program_entity_map pem ON r.id = pem.entity_id AND pem.entity_type = 'responsibility' --joins for search LEFT JOIN programs p ON pem.program_id = p.id AND p.active = 1 AND p.trashed = 0 LEFT JOIN users u ON ( ra.assignee_id = u.id OR r.assignor_id = u.id ) AND u.status = 'active' LEFT JOIN responsibility_centers rc ON rc.id = ANY(r.responsibility_centers) AND rc.status = 'active' WHERE ra.status IN ('active') AND r.status IN ('active', 'lc_completed') AND r.is_folder = 0 AND r.organization_id = 1 AND r.trashed_at IS NULL AND ( radd.window_end_at > '2023-02-01' OR r.created_at > '2023-02-01' ) AND ( radd.window_start_at < '2023-11-30' OR r.created_at < '2023-11-30' ) AND ( ra.assignee_id = 2 OR rad.to_user_id = 2 ) -- conditions for search AND ( r.control_id LIKE '%searchText%' OR p.name LIKE '%searchText%' OR rc.name LIKE '%searchText%' OR u.name LIKE '%searchText%' OR r.title LIKE '%searchText%' --ToDo frequency text search ) -- filter conditions AND pem.program_id IN (1, 2) AND r.responsibility_centers && ARRAY [1, 2] AND ( ra.assignee_id IN (1, 2) OR ra.assignee_id IN ( SELECT user_id FROM user_group_map WHERE group_id IN (1, 2) ) ) GROUP BY r.id ORDER BY min(radd.due_date) ASC LIMIT 100 OFFSET ((1 -1) * 100)
Editor is loading...
Leave a Comment