Moodle Modules by Course Detail V1

modules_by_course_detail_v1

select c.id as course_id, 
  c.visible as visible,
  c.shortname as course_name, 
  fac.userid as instructor,
  COALESCE(q.all_quizs,0) as all_quizs, 
  COALESCE(ass.all_assignments,0) as all_assignmenets, 
  COALESCE(wiki.all_wikis,0) as all_wikis,
  COALESCE(r.all_resources,0) as all_resources,
  COALESCE(u.all_urls,0) as all_urls,
  COALESCE(pa.all_pages,0) as all_pages,
  COALESCE(qr.all_questionnaires,0) as all_questionnaires,
  COALESCE(wor.all_workshops,0) as all_workshops,
  COALESCE(bl.all_posts,0) as course_blogs,
  COALESCE(ch.all_chats,0) as all_chatrooms,
  COALESCE(gl.all_glossarys,0) as all_glossarys,
  COALESCE(le.all_lessons,0) as all_lessons,
  COALESCE(cho.all_choices,0) as all_choices,
  COALESCE(fee.all_feedback,0) as all_feedback,
  COALESCE(fr.all_forums,0) as all_forums
  
  from 
    (
      select * from mdl_course as c 
        where c.idnumber like '%-2124'
    ) as c 

    left join 
    (
      select c.id as id, c.shortname, count(*) as all_quizs
        from mdl_quiz as q join mdl_course as c on (q.course=c.id)
        group by c.shortname
    ) as q on (q.id=c.id)

    left join
    (
      select c.id as id, c.shortname, count(*) as all_assignments
        from mdl_assignment as a join mdl_course as c on (a.course=c.id)
        group by c.shortname
    ) as ass on (ass.id=c.id)

    left join
    (
      select c.id as id, c.shortname, count(*) as all_wikis
        from mdl_wiki as w join mdl_course as c on (w.course=c.id)
        group by c.shortname
    ) as wiki on (wiki.id=c.id)
    
    left join
    (
      select c.id, c.shortname, count(*) as all_resources
      from mdl_resource as r join mdl_course as c on (r.course=c.id)
      group by c.shortname
    ) as r on (r.id=c.id)
  
    left join 
    (
      select c.id, c.shortname, count(*) as all_urls
      from mdl_url as u join mdl_course as c on (u.course=c.id)
      group by c.shortname
    ) as u on (u.id=c.id)

    left join
    (
      select c.id as id, c.shortname, count(*) as all_folders
      from mdl_folder as f join mdl_course c on (f.course=c.id)
      group by c.shortname
    ) as fo on (fo.id=c.id)
    
    left join
    (
      select c.id as id, c.shortname, count(*) as all_pages
      from mdl_page as p join mdl_course c on (p.course=c.id)
      group by c.shortname

    ) as pa on (pa.id=c.id)

    left join
    (
      select c.id as id, c.shortname, count(*) as all_questionnaires
      from mdl_questionnaire as q join mdl_course as c on (q.course=c.id)
      group by c.shortname

    ) as qr on (qr.id=c.id)
     
    left join
    (
      select c.id as id, c.shortname, count(*) as all_workshops
      from mdl_workshop as mw join mdl_course as c on (mw.course=c.id)
      group by c.shortname
    ) as wor on (wor.id=c.id)

    left join
    (
      select c.id as id, c.shortname, count(*) as all_posts
      from mdl_post as p join mdl_course as c on (p.courseid=c.id)
      where p.module = 'blog'
      group by c.shortname
    ) as bl on (bl.id=c.id)

    left join
    (
      select c.id as id, c.shortname, count(*) as all_chats
      from mdl_chat as t join mdl_course as c on (t.course=c.id)
      group by c.shortname
    ) as ch on (ch.id=c.id)
    
    left join
    (
      select c.id as id, c.shortname, count(*) as all_glossarys
      from mdl_glossary as g join mdl_course as c on (g.course=c.id)
      group by c.shortname
    ) as gl on (gl.id=c.id)

    left join
    (
      select c.id as id, c.shortname, count(*) as all_lessons
      from mdl_lesson as ml join mdl_course as c on (ml.course=c.id)
      group by c.shortname
    ) as le on (le.id=c.id)

    left join
    (
      select c.id as id, c.shortname, count(*) as all_choices
      from mdl_choice as o join mdl_course as c on (o.course=c.id)
      group by c.shortname
    ) as cho on (cho.id=c.id)
    
    left join
    (
      select c.id as id, c.shortname, count(*) as all_feedback
      from mdl_feedback as f join mdl_course as c on (f.course=c.id)
      group by c.shortname
    ) as fee on (fee.id=c.id)

    left join
    (
      select c.id as id, c.shortname, count(*) as all_forums
      from mdl_forum as f join mdl_course as c on (f.course=c.id)
      group by c.shortname

    ) as fr on (fr.id=c.id)



      

  left join 
    (
     select ue.userid, mu.username, c.id as course_id from mdl_user_enrolments ue
      join mdl_enrol e on (ue.enrolid=e.id)
      join (select * from mdl_course c where c.idnumber like '%-2124') c on (c.id=e.courseid)
      join (select * from mdl_context where contextlevel=50) ct on (ct.instanceid=c.id)
      join (select * from mdl_role_assignments where component='enrol_engine') ra on (ra.contextid=ct.id and ue.userid=ra.userid)
      join mdl_role r on (ra.roleid = r.id)
      join mdl_user as mu on (mu.id = ue.userid)
      where c.idnumber like '%-2124'
      and r.name = 'Instructor'
      and e.enrol like 'engine'
      group by c.id
    ) as fac on (fac.course_id = c.id)