Skip to content

Instantly share code, notes, and snippets.

@sroehrl
Created September 19, 2019 00:08
Show Gist options
  • Save sroehrl/3fd77c68c9d63bdc64b65abcb6d195d0 to your computer and use it in GitHub Desktop.
Save sroehrl/3fd77c68c9d63bdc64b65abcb6d195d0 to your computer and use it in GitHub Desktop.
The impossible
SELECT
academy.name as academy,
GROUP_CONCAT(DISTINCT competency.name SEPARATOR ', ') as competency,
core_lo.name as lo,
co.name as co,
csfv.value as proficiency,
lo_non.name as non_core_lo,
other_co.name as other_cos
FROM learning_objective_group academy
JOIN learning_objective_group_relation academy_r ON academy_r.learning_objective_group_id = academy.id AND academy_r.delete_date IS NULL
JOIN learning_objective core_lo ON core_lo.id = academy_r.learning_objective_id AND core_lo.delete_date IS NULL
/* competency? */
LEFT JOIN (
SELECT competency.name, competency.id, core.id as lo_id
FROM learning_objective_group_relation r
JOIN learning_objective_group competency ON competency.id = r.learning_objective_group_id AND competency.delete_date IS NULL
JOIN learning_objective core ON core.id = r.learning_objective_id
WHERE r.delete_date IS NULL
) as competency ON competency.id != academy.id AND competency.lo_id = core_lo.id
/* Content objects & proficiency*/
LEFT JOIN content_object_learning_objective co_lo ON co_lo.learning_objective_id = core_lo.id AND co_lo.delete_date IS NULL
LEFT JOIN content_object co ON co.id = co_lo.content_object_id AND co.delete_date IS NULL
LEFT JOIN content_object_customer_specific_field_value csfv ON csfv.content_object_id = co.id AND csfv.customer_specific_field_id = UNHEX("DB802EA9AED711E99B6602313DCACE58") AND csfv.delete_date IS NULL
/* NON core LO */
LEFT JOIN content_object_learning_objective co_lo2 ON co_lo2.learning_objective_id != core_lo.id AND co_lo2.content_object_id = co.id AND co_lo2.delete_date IS NULL
LEFT JOIN learning_objective as lo_non ON lo_non.id = co_lo2.learning_objective_id AND lo_non.name NOT IN('Envision','Engage','Empower','Enrich')
# Other cos having this los
LEFT JOIN content_object as other_co ON other_co.id != co.id AND other_co.id = co_lo.content_object_id AND other_co.delete_date IS NULL
WHERE academy.name REGEXP '^(Envision|Engage|Empower|Enrich)' AND academy.delete_date IS NULL
GROUP BY lo, non_core_lo
ORDER BY academy, competency, proficiency
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment