SQL - Get total quizzes created by particular teacher

SQL - Get total quizzes created by particular teacher

by Rizal Hadi -
Number of replies: 1

I want to write a SQL query to get total number of a particular activity type created by a particular teacher in a particular course. For example is total number of quizzes.


activity type: Quiz

course ID = 1758 

teacher user ID = 111062 


SELECT COUNT(1) AS total_genuine_quiz_by_the_teacher

FROM mdl_quiz q, mdl_logstore_standard_log l

WHERE q.course = '1758'

AND l.courseid = q.course

AND l.eventname LIKE '%\mod_quiz%'

AND l.component = 'mod_quiz'

AND l.crud = 'c'

AND l.edulevel = '1'

AND l.userid = '111062';


..but it always return 0 for most courses I tried and seems doesn't right. May be I'm not quite understand how Moodle keep/store ID who created an activity in its log or may be I miss something here. Answer in anywhere is highly appreciated!

Thank you.



Average of ratings: -
In reply to Rizal Hadi

Re: SQL - Get total quizzes created by particular teacher

by Elizabeth Dalton -

I'm not sure where your query is going wrong, but you do have a lot of overlapping constraints in your WHERE clause. Try something like this:

SELECT COUNT(*)
FROM prefix_logstore_standard_log AS l
JOIN prefix_course_modules AS cm ON cm.id = l.objectid
JOIN prefix_modules AS m ON m.id = cm.module
WHERE l.eventname = '\\core\\event\\course_module_created'
AND m.name = 'quiz'
AND l.userid = '111062'


Average of ratings: -