Create placeholder curriculum for orphaned results

Overview

Organization Admins may not be able to delete users who are associated with results orphaned from a curriculum.

SQL

INSERT IGNORE INTO

curriculums (films_class_id,structure_id,`name`,is_default,is_visible)

SELECT

c2.films_class_id,

s.id structure_id,

CONCAT("Placeholder Curriculum: ",s.name) `name`,

0 is_default,

0 is_visible

FROM organizations o

LEFT JOIN users u

ON u.organization_id = o.id

LEFT JOIN enrollments e

ON e.user_id = u.id

LEFT JOIN results r

ON r.enrollment_id = e.id

LEFT JOIN structure_objects so

ON so.learning_object_id = r.learning_object_id

LEFT JOIN structure_sections ss

ON ss.id = so.structure_section_id

LEFT JOIN structures s

ON s.id = ss.structure_id

LEFT JOIN curriculums c

ON c.structure_id = s.id

LEFT JOIN courses co

ON co.id = s.course_id

LEFT JOIN organization_course_catalogs occ

ON occ.course_id = co.id AND occ.organization_id = o.id

LEFT JOIN curriculums c2

ON c2.id = e.curriculum_id

WHERE TRUE

AND o.id = 1

AND ss.structure_id IS NOT NULL

AND c.id IS NULL

GROUP BY s.id