oer_catalog.sql

SELECT
        lm.instanceid
       ,(SELECT DISTINCT m.data
        FROM mdl_local_metadata as m
               JOIN mdl_local_metadata_field AS mf ON m.fieldid = mf.id AND mf.contextlevel = 70
        WHERE mf.shortname = 'ID' and m.instanceid = cm.id) as 'Unique resource ID'
       ,(SELECT DISTINCT m.data
        FROM mdl_local_metadata as m
               JOIN mdl_local_metadata_field AS mf ON m.fieldid = mf.id AND mf.contextlevel = 70
        WHERE mf.shortname = 'teacherremarks' and m.instanceid = cm.id) as 'MD - Teacher intro'

        #,COUNT(*) 'metadata fields'

#     ,CONCAT('<a target="_new" href="%%WWWROOT%%/local/metadata/index.php?id=',
#             lm.instanceid,'&action=moduledata&contextlevel=70">',
#             m.name, '</a>') AS 'Edit metadata'
      ,m.name
      ,CASE m.name
         WHEN 'assign' THEN (SELECT m.intro FROM mdl_assign m WHERE m.id = cm.instance)
         WHEN 'forum' THEN (SELECT m.intro FROM mdl_forum m WHERE m.id = cm.instance)
         WHEN 'quiz' THEN (SELECT m.intro FROM mdl_quiz m WHERE m.id = cm.instance)
         WHEN 'questionnaire' THEN (SELECT m.intro FROM mdl_questionnaire m WHERE m.id = cm.instance)
         WHEN 'hvp' THEN (SELECT m.intro FROM mdl_hvp m WHERE m.id = cm.instance)
         WHEN 'resource' THEN (SELECT m.intro FROM mdl_resource m WHERE m.id = cm.instance)
         WHEN 'page' THEN (SELECT m.intro FROM mdl_page m WHERE m.id = cm.instance)
         WHEN 'folder' THEN (SELECT m.intro FROM mdl_folder m WHERE m.id = cm.instance)
         WHEN 'ejsapp' THEN (SELECT m.intro FROM mdl_ejsapp m WHERE m.id = cm.instance)
         WHEN 'url' THEN (SELECT m.intro FROM mdl_url m WHERE m.id = cm.instance)
       END AS 'OER module intro, for students'

FROM mdl_local_metadata lm
  JOIN mdl_local_metadata_field lmf ON lmf.id = lm.fieldid
  JOIN mdl_course_modules cm ON cm.id = lm.instanceid
  JOIN mdl_modules m ON m.id = cm.module
  JOIN mdl_course c ON c.id = cm.course
  JOIN mdl_course_categories cc ON cc.id = c.category
WHERE lmf.contextlevel = 70
    #AND lmf.shortname = 'teacherremarks'
    AND lmf.shortname = 'ID'
    #AND cc.path like '/2/%'
    AND c.category IN (3, 7, 8, 93) );