Hello,
I am trying to perform a SQL query where show me:
- the course name
- quiz name
- questions
- student response
- question grade
- final grade.
MOODLE version: 4.0.2
I manage to reach to the following query:
---
SELECTprefix_user.id AS UID,
prefix_user.username AS User,
prefix_course.id AS CID,
prefix_course.fullname AS course_name,
prefix_quiz.name AS quiz,
prefix_question.name AS question,
prefix_question_answers.fraction,
prefix_question_attempts.rightanswer AS 'correct answer',
prefix_question_attempts.responsesummary AS Answer1,
prefix_question_answers.answer AS Answer2,
prefix_quiz_attempts.sumgrades AS 'Qualification',
prefix_quiz.grade AS 'Max Qualification'
FROM
prefix_user prefix_user
JOIN
prefix_quiz_attempts prefix_quiz_attempts ON prefix_user.id = prefix_quiz_attempts.userid
JOIN
prefix_quiz prefix_quiz ON prefix_quiz.id = prefix_quiz_attempts.quiz
JOIN
prefix_course prefix_course ON prefix_course.id = prefix_quiz.course
JOIN
prefix_question_attempts prefix_question_attempts ON prefix_quiz_attempts.uniqueid = prefix_question_attempts.questionusageid
JOIN
prefix_question prefix_question ON prefix_question.id = prefix_question_attempts.questionid
JOIN
prefix_question_answers prefix_question_answers ON prefix_question_answers.question = prefix_question.id
WHERE
prefix_user.id = {userid}
AND prefix_course.id = {courseid}
GROUP BY
prefix_user.id,
prefix_user.username,
prefix_course.id,
prefix_course.fullname,
prefix_question.id,
prefix_question.name,
prefix_quiz_attempts.sumgrades,
prefix_question_attempts.responsesummary;
---
But I can't solve a way to get the grade for the answer given by the student, any idea?