Problem with SQL query combining module and log info

Problem with SQL query combining module and log info

by Elizabeth Dalton -
Number of replies: 2

I don't know if this is the right place for this question-- please direct me elsewhere if not. I encountered this problem while using the Configurable Reports plugin, but I don't think that matters for this question.

I'm trying to write a report that shows, for a list of courses, whether they are ready for the start of the new term. At our college we copy existing courses forward into new shells for many instructors; for some courses, the instructor starts from a blank template. So I need to show columns for how many of certain kinds of activities and resources have been created for each course, and I also want to be able to show how many edits the assigned instructor has made to the course. I want to use this report just as a quick overview so I know if I need to contact an instructor and offer help.

I can write reports that summarize module contents, and I can write reports that summarize log information, but when I try to combine the two I get very strange results. Not all log entries show up, and module counts are distorted. My code is below (with just one example course in the list). Can anyone offer any suggestions for how to improve it?

 
SELECT 

c.fullname AS Course
, CONCAT(u.lastname,', ', u.firstname) AS Teacher

, SUM( IF(cm.module = 9, 1,0)) AS Forums
, SUM( IF(cm.module = 16,1,0)) AS Quizzes
, SUM( IF(cm.module IN ('1','2'),1,0)) AS Assignments
, SUM( IF(cm.module = 15,1,0)) AS Pages
, SUM( IF(cm.module = 17,1,0)) AS Files
, SUM( IF(cm.module = 20,1,0)) AS Links
, COUNT( l.id) AS Edits

FROM prefix_course AS c
JOIN prefix_context AS ctx ON ctx.instanceid = c.id
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id AND ra.roleid = 3
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.visible = 1

LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.cmid = cm.id AND l.action NOT LIKE "view%" 
%%FILTER_STARTTIME:l.TIME:>%% %%FILTER_ENDTIME:l.TIME:<%%

WHERE

c.shortname IN ('ENG510.1OL-FL13')
GROUP BY c.shortname, u.id

Results of combined and separate queries:

Combined:

Combined (incorrect)

logs only:

logs only

Modules only:

modules only

Note that there really is only one quiz, and the instructor really did make 255 edits (all logged actions that don't match 'view%'). Is there any way to show both of these facts accurately in the same report, or do I need to do this with two separate reports or a UNION query or something like that?

Average of ratings: -
In reply to Elizabeth Dalton

Re: Problem with SQL query combining module and log info

by Juan F -

Elizabeth, this is a great query.  Did you ever find a solution?

Average of ratings: -
In reply to Juan F

Re: Problem with SQL query combining module and log info

by Elizabeth Dalton -

Hi Chris,

I don't remember what the problem ended up being with that query-- it might have been the use of date filters, which I eventually dropped. Here are the queries that eventually were used instead:

-----

SELECT CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.fullname,'</a>') AS Course

,RIGHT(c.idnumber,2) AS Type

, substring_index(substr(c.shortname FROM locate('.',c.shortname)+1),'-',1) AS Section
 
,(SELECT CONCAT(u.lastname,', ', u.firstname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%quiz%') AS Quizzes
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments

,(SELECT COUNT(prefix_page.id)
FROM prefix_page
JOIN prefix_course ON prefix_course.id = prefix_page.course WHERE c.id = prefix_page.course) AS 'Pages'

,(SELECT COUNT(prefix_resource.id)
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course) AS Files

,(SELECT COUNT(prefix_url.id)
FROM prefix_url
JOIN prefix_course ON prefix_course.id = prefix_url.course WHERE c.id = prefix_url.course) AS Links

,(SELECT FROM_UNIXTIME(MAX(prefix_resource.timemodified))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS SyllabusDate

,(SELECT TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(MAX(prefix_resource.timemodified)))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS DaysAgo


FROM prefix_course AS c
WHERE RIGHT(c.idnumber,2) IN ('OL', 'BL', 'HY') %%FILTER_CATEGORIES:c.category%%

-----

SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS Course

,RIGHT(c.idnumber,2) AS Type

, substring_index(substr(c.shortname FROM locate('.',c.shortname)+1),'-',1) AS Section
 
,(SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/user/view.php',CHAR(63),'id=',u.id,'">',u.lastname,', ', u.firstname,'</a>')
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Instructor'

,(SELECT IF((u2.description IS NULL) OR (u2.description LIKE ''),'NO', 'YES')
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u2 ON u2.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Profile Has Bio'

,(SELECT IF(u3.picture > 0,'YES','NO')
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u3 ON u3.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Profile Has Picture'

, IF(((bpi.visible IS NULL) OR (bpi.visible !=0)) AND ((bpm.visible IS NULL) OR (bpm.visible !=0)) AND ((bpa.visible IS NULL) OR (bpa.visible !=0)) AND ((bpr.visible IS NULL) OR (bpr.visible !=0)),'YES','NO') AS 'Required blocks visible'
#, IF((bpm.visible IS NULL) OR (bpm.visible !=0),'YES','NO') AS 'Messages block visible'
#, IF((bpa.visible IS NULL) OR (bpa.visible !=0),'YES','NO') AS 'activities block visible'
#, IF((bpr.visible IS NULL) OR (bpr.visible !=0),'YES','NO') AS 'research block visible'

, IF(SUM(IF(bi.configdata LIKE 'Tzo4OiJzdGRDbGFzcyI6Mzp7czo1OiJ0aXRsZSI7czoxODoiSW5zdHJ1Y3RvciBEZXRhaWxzI%',1,0)),'YES','') AS 'Instructor Details Block visible' #this is a bit of a hack, we use it to detect an HTML block w/default text

, IF(bi.configdata LIKE '%ZGl0IHRoaXMgYmxvY2s%','NO','') AS 'Instructor Details Block Updated' # has string 'dit this block'

, IF(COUNT(bi.id) -  SUM(IF(bi.configdata LIKE 'Tzo4OiJzdGRDbGFzcyI6Mzp7czo1OiJ0aXRsZSI7czoxODoiSW5zdHJ1Y3RvciBEZXRhaWxzI%',1,0)),'YES','') AS 'possible extra instructor blocks' # this is also a hack, looks for block with name beginning "Instructor"...

, IF(c.format='topcoll','YES', c.format) AS 'Collapsed Topics course format'
, IF(cfo.value = 2, 'YES','NO') AS 'weeks structure'

, cfw.value AS 'weeks defined in course settings'

, COUNT(DISTINCT IF(((cs.name IS NOT NULL) AND (cs.visible = 1) AND (cs.section != '0') AND (cs.sequence IS NOT NULL)),cs.id,NULL)) AS '# of weeks named & visible (includes orphans)'

, COUNT(DISTINCT IF(cm.module = '9', cm.id, NULL)) AS 'Forums'
, COUNT(DISTINCT IF(cm.module = '9' ,cs.id , NULL)) AS 'Weeks with Forum'

, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) AS 'Graded Activities'
, COUNT(DISTINCT IF(gi.id, cs.id, NULL)) AS 'Graded Weeks'
, COUNT(DISTINCT mgi.id) AS 'Manual Grade Items'

, COUNT(DISTINCT IF((gi.id IS NULL) AND (cm.module != '12'),cm.id,NULL)) AS 'Ungraded Resources'
, COUNT(DISTINCT IF((gi.id IS NULL) AND (cm.module != '12'), cs.id, NULL)) AS 'Weeks with Resources'

#,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
 
#,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%quiz%') AS Quizzes
 
#,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%assign%') AS Assignments

#,(SELECT COUNT(prefix_resource.id) FROM prefix_resource JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course) AS Files

#,(SELECT COUNT(prefix_url.id) FROM prefix_url JOIN prefix_course ON prefix_course.id = prefix_url.course WHERE c.id = prefix_url.course) AS Links

,(SELECT FROM_UNIXTIME(MAX(prefix_resource.timemodified))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS SyllabusDate

,(SELECT TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(MAX(prefix_resource.timemodified)))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS DaysAgo


FROM prefix_course AS c
JOIN prefix_course_categories as cc ON c.category = cc.id
JOIN prefix_context AS ctxx ON c.id = ctxx.instanceid

LEFT JOIN prefix_block_positions AS bpi ON bpi.contextid = ctxx.id AND bpi.blockinstanceid = '43692' # mooprofile
LEFT JOIN prefix_block_positions AS bpm ON bpm.contextid = ctxx.id AND bpm.blockinstanceid = '43962' # messages
LEFT JOIN prefix_block_positions AS bpa ON bpa.contextid = ctxx.id AND bpa.blockinstanceid = '43963' # activities
LEFT JOIN prefix_block_positions AS bpr ON bpr.contextid = ctxx.id AND bpr.blockinstanceid = '38368' # html research help

JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.visible = 1 AND cs.sequence IS NOT NULL
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
JOIN prefix_modules AS m ON m.id = cm.module
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = cm.instance

# attach manual grade items to course-- they don't have modules
LEFT JOIN prefix_grade_items AS mgi ON mgi.courseid = c.id and mgi.itemtype = 'manual'

JOIN prefix_course_format_options AS cfo ON cfo.courseid = c.id AND cfo.name = 'layoutstructure'
JOIN prefix_course_format_options AS cfw ON cfw.courseid = c.id AND cfw.name = 'numsections'

LEFT JOIN prefix_block_instances AS bi ON bi.parentcontextid = ctxx.id AND bi.blockname = 'html' AND (bi.configdata LIKE '%SW5zdHJ1Y3Rvc%' or bi.configdata LIKE '%bnN0cnVjdG9y%')

WHERE RIGHT(c.idnumber,2) IN ('OL', 'BL', 'HY')
 AND substring(cc.path,2,2) IN ('26') # Staging
AND ctxx.contextlevel = 50

GROUP BY c.shortname


Average of ratings: -