Moodle SQL查询检索在过去90天中注册的所有用户

Question

我需要一个SQL查询，它将使所有在过去90天中注册的课程的用户都没有被暂停。

我做了一些调查，发现如下：

SELECT DISTINCT u.id AS userid, c.id AS courseid
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 (ue.timeend = 0 OR ue.timeend > NOW()) AND ue.status = 0

通过将AND courseid = 11添加到查询的末尾，我可以返回特定课程的结果，但我仍然在为日期范围而奋斗。

我试过用：

AND ue.timecreated BETWEEN NOW() - INTERVAL 90 DAY AND NOW()

但这是没有返回结果，我甚至尝试添加2000天范围，但它没有返回任何

这是我当前修改的查询：

SELECT DISTINCT u.id AS userid, c.id AS courseid, DATE_FORMAT(ue.timecreated, '%m/%d/%Y')
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 (
ue.timeend =0
OR ue.timeend > NOW()
)
AND ue.status =0
AND ue.timecreated BETWEEN NOW() - INTERVAL 1200 DAY AND NOW()
AND courseid = 11

一旦我移除包含日期范围的第二行，我就会收到课程id 11中的所有结果。

基本上我需要：

在过去90天内注册参加某门特定课程的所有用户的列表，这些用户没有被暂停或删除。

结果如下：

course id: 20
user id: 200, 533, 221, 22, 356

Answer 1

解决办法是：

SELECT DISTINCT u.id AS userid, c.id AS courseid, DATE_FORMAT(FROM_UNIXTIME(ue.timecreated),'%m/%d/%Y') AS timecreated
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 (
ue.timeend =0
OR ue.timeend > NOW( )
)
AND ue.status =0
AND courseid =11
AND FROM_UNIXTIME(ue.timecreated) BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()

Answer 2

Moodle中的时间存储为整数，是UTC unix时间戳。因此，MySql中的date函数无法工作--除非使用FROM_UNIXTIME()进行转换。

如果您希望所有的用户注册，而不仅仅是学生，那么这就更简单了。

SELECT ue.id, e.courseid, ue.userid, e.enrol AS enrolmethod, FROM_UNIXTIME(ue.timecreated)
FROM mdl_user_enrolments ue
JOIN mdl_enrol e ON e.id = ue.enrolid AND e.status = 0
JOIN mdl_user u ON u.id = ue.userid AND u.deleted = 0 AND u.suspended = 0
WHERE ue.timecreated >= 90 * 24 * 60 * 60 AND ue.status = 0

但是，用户可以使用多个方法注册。因此，如果您只想要用户if和courseid，那么使用一个组而不是一个单独的组。

SELECT CONCAT(e.courseid, '_', ue.userid) AS uniqueid, e.courseid, ue.userid, FROM_UNIXTIME(MAX(ue.timecreated))
FROM mdl_user_enrolments ue
JOIN mdl_enrol e ON e.id = ue.enrolid AND e.status = 0
JOIN mdl_user u ON u.id = ue.userid AND u.deleted = 0 AND u.suspended = 0
WHERE ue.status = 0
GROUP BY e.courseid, ue.userid
HAVING MAX(ue.timecreated) >= 90 * 24 * 60 * 60