Configurable report - Users who have not attempted a quiz

Configurable report - Users who have not attempted a quiz

by Aidan Dauphin -
Number of replies: 3

Hi All,

I do realise there was another discussion on this from 2017 but it is locked and unable to add to the post.

Apologies in advance I am new to this community smile

As per the above, I am wanting to write a configurable report that shows only users that have not attempted the quiz. Below is the SQL I have put together to try and get what I am after. If anyone could offer me some assistance would be greatly appreciated. Code below so far

-----

Select 

c.id AS "Course ID",

c.fullname AS "Course Name",

cm.id as "Quiz ID",

q.name AS "Quiz Name",

u.idnumber AS "User ID",

CONCAT(u.firstname, ' ', u.lastname) AS "User's name",

ss.attempt AS "Attempt"



FROM

prefix_quiz q

INNER JOIN prefix_course c ON q.course = c.id

INNER JOIN prefix_course_categories cc ON c.category = cc.id

INNER JOIN prefix_course_modules cm ON cm.instance = q.id and cm.module = 16

INNER JOIN prefix_enrol e ON e.courseid = c.id

INNER JOIN prefix_user_enrolments ue on ue.enrolid = e.id 

INNER JOIN prefix_user u ON u.id = ue.userid

FULL OUTER JOIN prefix_scorm_aicc_session ss ON ss.userid = u.id

        LEFT JOIN prefix_scorm s ON s.course = c.id

where 

c.fullname = 'FIT1051 2021 Sem 2 (Australia)' 

GROUP BY 

      c.id,

      c.fullname,

      cm.id,

     q.name,

     u.id,

     u.firstname,

     u.lastname,

    ss.attempt


Average of ratings: -
In reply to Aidan Dauphin

Re: Configurable report - Users who have not attempted a quiz

by Randy Thornton -
Picture of Documentation writers

If you are looking for users who have not yet attempted a quiz, then you will need to look into the quiz_attempts table - which will show you who _has_ attempted the quiz. From there, you will need to deduce the enroled users who have no such attempts in the table. Or, to put it another way, you want all the enroled users in the course who have no entry in the quiz_attempts table.

Some of the queries on the Ad-hoc contributed reports page may help you out, such as this one https://docs.moodle.org/311/en/ad-hoc_contributed_reports#List_the_users_that_did_not_take_the_Quiz or this one https://docs.moodle.org/311/en/ad-hoc_contributed_reports#Student_responses_.28answers.29_to_quiz_questions.

I don't understand why your query references the scorm tables, which not related to quizzes in any way.
Average of ratings: Useful (1)
In reply to Randy Thornton

Re: Configurable report - Users who have not attempted a quiz

by Aidan Dauphin -
Hi Randy,

Really appreciate you replying to my thread smile
In regards to using scorm tables I was reading another forum which as you mentioned lead me down the completely wrong path.

I jumped onto the first link you posted above and its mostly working which is great.

I have taken the code and altered it to more what I am after, as this code points to one course where I am hoping to get it to go through all courses in a certain category. Which I will post the code below. If I use there logic and point to one-course bingo it returns the correct users who have not attempted the quiz.. If I use my changed logic from each course it drops like 5 or so users from the results, it's like the query is not reading through the full table??

My new code below.

SELECT DISTINCT

c.fullname AS "Course Name",
u2.idnumber AS "ID Number",
u2.firstname AS "First Name",
u2.lastname AS "Last Name"

FROM

prefix_user_enrolments ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_course_categories cc ON c.category = cc.id
JOIN prefix_user AS u2 ON u2.id = ue.userid

WHERE
(cc.path LIKE '/68/3170/3177/3210%')

AND ue.userid NOT IN (SELECT qa.userid
FROM prefix_quiz_attempts AS qa
JOIN prefix_quiz AS q ON qa.quiz = q.id
JOIN prefix_course c on q.course = c.id
JOIN prefix_course_categories cc ON c.category = cc.id

WHERE
(cc.path LIKE '/68/3170/3177/3210%')
AND c.category = cc.id) -- Note only.. Origianl code = c.fullname = 'xxxxxx' this is when it works as expected
AND
u2.idnumber LIKE 'XXXXXXIDP:Student:%'

ORDER BY c.fullname

Side not. One of the courses using the original code returns 37 uses, when using the adjusted code it return 28 uses.

Sorry for the XXX's just need to hide some details
Average of ratings: -
In reply to Aidan Dauphin

Re: Configurable report - Users who have not attempted a quiz

by Randy Thornton -
Picture of Documentation writers

I think the issue is probably the location of the closing parens in the second where clause in the sub select. It seems to be out of place and close the sub select too early. That's probably why it worked with the original code okay.

When troubleshooting such issues I try to reduce the query to the absolute basics. In this case, I would remove all the category parts and just put in a simple course id to a course that I can manually verify and check the results against. So, just a c.id= will let me check that.

Note also that, with more than one course, this query will return only users who have no quizzes in any courses, so they many have done one quiz in one course but not in the others. Also, it returns all enrolled roles, so both teachers and students, since it is just looking at enrolments and not at role assignments. (You can always add that in later.)

The following stripped down one works for me and shows the logic of the query more clearly:

SELECT DISTINCT
c.fullname AS "Course Name",
u2.username AS "User"

FROM prefix_user_enrolments ue
JOIN prefix_enrol AS e ON e.id = ue.enrolid
JOIN prefix_course AS c ON c.id = e.courseid
JOIN prefix_user AS u2 ON u2.id = ue.userid
WHERE c.id = 2
AND ue.userid NOT IN 
 (SELECT qa.userid
  FROM prefix_quiz_attempts AS qa
  JOIN prefix_quiz AS q ON qa.quiz = q.id
  JOIN prefix_course c on q.course = c.id
  WHERE c.id = 2)
  
ORDER BY c.fullname, u2.username
Average of ratings: Useful (1)