Skip to content

Instantly share code, notes, and snippets.

@mikehas
Created July 30, 2012 01:12
Show Gist options
  • Save mikehas/3203120 to your computer and use it in GitHub Desktop.
Save mikehas/3203120 to your computer and use it in GitHub Desktop.
Moodle 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment