SQL query ignoring time clause

Re: SQL query ignoring time clause

by Gal Sapir -
Number of replies: 1

Tried this:

SELECT category, l.courseid AS courseId, c.fullname AS 'Course Name', origin, COUNT( l.userid) AS 'Users Hits', DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%m') 'Month'
FROM mdl_logstore_standard_log AS l
INNER JOIN mdl_course AS c ON l.courseid = c.id
JOIN mdl_role_assignments AS ra on ra.userid = l.userid
WHERE FROM_UNIXTIME(l.timecreated) BETWEEN '2018-09-01 00:00:00' AND '2019-02-28 00:00:00'
AND ra.roleid = 5
GROUP BY courseId, Month, origin
ORDER by category, Month
This returns reasonable results, however, I wasn't able to incorporate the following into the statement:
JOIN mdl_context AS ctx on ra.contextid = ctx.id
WHERE ctx.instanceid = c.id

This was returning an error. Does anyone have an idea why would that be?  It would be helpful to narrow the results down. I am not sure that the context is the right way to do that. Any advice will be welcome.  

Average of ratings: -
In reply to Gal Sapir

Re: SQL query ignoring time clause

by Gal Sapir -

This is the final Query I ended up with, it works but had to be adjusted. The main reason for the error messages, as I understand it, was the time it took the server to reply. So essentially the query returned too large a set, the server timed out and returned an error. A workaround was to break it into separate groups using courses ids. We took all 300+ courses and divided them into groups of about 40, and the server started to return results. 

This query also contains a 'hits per student' column which uses a subquery to divide the number of hits by the number of students on the course., It makes the query a bit large and it would be great to optimise that. Nevertheless, the query works well and returns results almost identical to the ones that come from Moodle's native Statistics and Reports.


#################################

SELECT category, cc.description AS 'Description', l.courseid AS 'courseId', c.fullname AS 'Course Name',  DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%m') 'Month', COUNT( l.userid) AS UsersHits, (SELECT Count( ra.userid ) AS Users FROM mdl_role_assignments AS ra

JOIN mdl_context AS ctx ON ra.contextid = ctx.id

WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students, (COUNT( l.userid) DIV (SELECT Count( ra.userid ) AS Users FROM mdl_role_assignments AS ra

JOIN mdl_context AS ctx ON ra.contextid = ctx.id

WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) +1) as 'Hits per student'

FROM mdl_logstore_standard_log AS l

INNER JOIN mdl_course AS c ON l.courseid = c.id

JOIN mdl_role_assignments AS ra on ra.userid = l.userid

JOIN mdl_context AS ctx on ra.contextid = ctx.id

JOIN mdl_course_categories AS cc on cc.id = c.category

WHERE FROM_UNIXTIME(l.timecreated) BETWEEN '2019-04-23 00:00:00' AND '2019-05-25 00:00:00' #-- half term dates ----

AND ra.roleid = 5

AND ctx.contextlevel = '50'

AND ctx.instanceid = c.id

AND c.id IN (886, 451, 309, 1111, 803, 834, 570, 616, 318, 1039, 261, 937, 1129, 938, 296, 939, 896, 310, 1114, 805, 1062, 1063, 566, 942, 1124, 940, 313, 941, 1087, 252, 1146, 1094, 954, 850, 319, 777, 636, 678, 928, 55, 1206, 776, 773, 775) #--sample courses id's here

GROUP BY courseId

ORDER by 'Hits per student' ASC

Average of ratings: -