コースを未完了のユーザ一覧の抽出を、メールの送信のためと一覧表示のため行いたいです。
小テストの未受験者および不合格者を抽出するSQL文を書くことはできたのですが、
コースの未完了者（該当ユーザのコース概要でコースの進捗が100％と表示されないユーザ）
を抽出するSQLもしくは参照テーブルなどを教えていただけないでしょうか。
mdl_course_completionsテーブルのtimecompleted列に
コースの完了状態が記録されているかと思ったのですが、
timecompleted列の値がすべてNULLになっておりました。
（コースを完了しているユーザのuseridで確認してもNULLになっております）
現在使用しているmoodleのバージョンは3.5.1です。
何卒よろしくお願いいたします。
Rick Jerz先生による下記投稿のSQLが参考になるかと思います。
[Moodle in English: Moodle SQL Report: Display Students Who have not Completed a Course]
https://moodle.org/mod/forum/discuss.php?d=377802#p1532024
お答え頂きありがとうございます。
URLを参照したのですが、思ったように動きません。
SELECT DISTINCT c.shortname AS CourseName,
u.firstname AS FirstName,
u.lastname AS LastName,
u.email AS Email,
gg.finalgrade AS FinalGrade,
c.idnumber AS CourseIDNum,
gi.courseid AS CourseNumber,
gg.userid AS StudentID,
3 AS moodleversion
FROM mdl_grade_items gi
JOIN mdl_grade_grades gg ON gi.id = gg.itemid
JOIN mdl_course c ON c.id = gi.courseid
JOIN mdl_user u ON u.id = gg.userid
JOIN mdl_context ct ON ct.instanceid = gi.courseid
JOIN mdl_role_assignments ra ON ra.userid = gg.userid
JOIN mdl_user_enrolments ue ON u.id = ue.userid
JOIN mdl_enrol e ON e.courseid = gi.courseid
WHERE gi.itemtype = "course"
AND gg.finalgrade IS NULL
AND u.suspended = 0
AND u.deleted = 0
AND ct.contextlevel = 50
AND ra.roleid = 5
AND ct.id = ra.contextid
AND ue.status = 0
AND e.status = 0
AND e.id = ue.enrolid
AND gi,courseid = 9
ORDER BY c.idnumber DESC,
u.lastname,
u.firstname
上記のSQL文を発行しても、結果が0件になってしまいました。
（コースのidは９で、1人のユーザをコースに登録、未受験の状態なので1件返ってきてほしいです）
今回コースにはテストを設けず、評点が存在しないコースで
コースを完了していないユーザの一覧を取得したいのですが、
finalgrade IS NULLでは誤っているのでしょうか。
もう少し綺麗に記述する必要があると思いますが、下記SQLではいかがでしょうか。
SELECT DISTINCT c.shortname AS CourseName,
u.id AS UserID,
u.username AS UserName,
u.firstname AS FirstName,
u.lastname AS LastName,
u.email AS Email
FROM mdl_user u
JOIN mdl_user_enrolments ue ON ue.userid=u.id
JOIN mdl_enrol e ON e.id = ue.enrolid
JOIN mdl_role_assignments ra ON ra.userid=u.id
JOIN mdl_context ct ON ct.id = ra.contextid
AND ct.contextlevel = 50
JOIN mdl_course c ON c.id = ct.instanceid
AND e.courseid = c.id
JOIN mdl_role r ON r.id = ra.roleid
AND r.shortname = 'student'
WHERE e.status = 0
AND u.suspended = 0
AND u.deleted = 0
AND NOT EXISTS (select * from mdl_course_completion_crit_compl WHERE userid = u.id)
AND c.id=9
ORDER BY u.id ASC
>またcoursemoduleidはどこのテーブルを参照すればよいのでしょうか。
下記のようにlib/moodlelib.phpの5068行目ではテーブル「mdl_course_modules」のカラム「id」を参照しています。
// Delete completion defaults.
$DB->delete_records("course_completion_defaults", array("course" => $courseid));
// Remove all data from availability and completion tables that is associated
// with course-modules belonging to this course. Note this is done even if the
// features are not enabled now, in case they were enabled previously.
$DB->delete_records_select('course_modules_completion',
'coursemoduleid IN (SELECT id from {course_modules} WHERE course=?)',
array($courseid));
回答いただきありがとうございます。
コース完了していたらmdl_course_completion_crit_compl テーブルに
その旨のデータ存在するんですね。
どうして存在しないのかよくわからないですね。
coursemoduleidはmdl_course_modulesのidを参照していたんですね。
教えていただきありがとうございます。
mdl_course_modules_completionテーブルの該当するコースの全coursemoduleidのものの、
complestateが1、2、３の状態のユーザを取ってくるように記述して解決しました。
いろいろ教えていただき本当にありがとうございました。
