mysql - Moodle 日期用户完成类(class)

Question

我试图获取用户上次完成类(class)的日期，我有以下有效的 SQL，但它给出了错误的日期。

我有所有模块的日期，但知道它们是错误的，因为有几个月了，而且我知道昨天完成了 1 门类(class)。

SELECT
u.username,
c.shortname,        
DATE_FORMAT(FROM_UNIXTIME(gi.timemodified), '%d/%m/%Y') AS 'date'
FROM moodle.user u
JOIN moodle.grade_grades g ON g.userid = u.id
JOIN moodle.grade_items gi ON g.itemid =  gi.id
JOIN moodle.course c ON c.id = gi.courseid
WHERE u.email = ?
GROUP BY c.shortname

Answer 1

类(class)完成情况应位于 mdl_course_completions

SELECT u.username,
       c.shortname,        
       cc.timecompleted
FROM mdl_course_completions cc
JOIN mdl_course c ON c.id = cc.course
JOIN mdl_user u ON u.id = cc.userid
WHERE u.email = :email