Moodle 上课程注册的 SQL 查询

SQL query for Courses Enrolment on Moodle

提问by d.yuk

I was looking for the proper SQL queries for retrieving all students enrolled in a certain course, or all courses a certain student has enrolled in, on Moodle.

我一直在寻找合适的 SQL 查询来检索在Moodle上注册的某个课程的所有学生，或某个学生注册的所有课程。

I have found a few solutions from the Internet, and most of them suggest joining these tables:
context, role_assignments, course, user, role

我从网上找了几个解决方案，大部分都建议加入这些表：
context、role_assignments、course、user、role

But then when I looked at the database, I found that there is a table named user_enrolments, and it seems to me that I could get the results by joining the following tables:
user_enrolments, user, course, enrol

但是后来看数据库的时候发现有个表叫user_enrolments，在我看来可以通过加入以下表来得到结果：
user_enrolments, user, course, enroll

For example,

例如，

SELECT u.id, c.id
FROM mdl_user u
INNER JOIN mdl_user_enrolments ue ON ue.userid = u.id
INNER JOIN mdl_enrol e ON e.id = ue.enrolid
INNER JOIN mdl_course c ON e.courseid = c.id

and

和

SELECT u.id, c.id
FROM mdl_user u
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid
INNER JOIN mdl_course c ON c.id = ct.instanceid
INNER JOIN mdl_role r ON r.id = ra.roleid
WHERE r.id = 5

(where 5 is the id for the role student)

（其中 5 是角色的 id student）

These 2 queries give me the SAMEset of results. (tested on a small set of data only)

这两个查询给了我相同的结果集。（仅在一小部分数据上测试）

So I would like to ask what are the differences between the two approaches?
Thank you for any assistance in advance.

所以我想问一下这两种方法有什么区别？
提前感谢您的任何帮助。

回答by davosmith

The first query gives you a list of users who are enroled on the course, whatever role they have assigned to them (it is possible to be enroled on a course and have no role assigned at all).

第一个查询为您提供了已注册课程的用户列表，无论他们分配给他们的角色是什么（可以注册课程但根本没有分配任何角色）。

The second query shows all the users who have role 5 assigned to them at the course level. It is possible (though unusual) to have a role assigned at the course level, without actually being enroled in the course itself.

第二个查询显示在课程级别为其分配了角色 5 的所有用户。有可能（尽管不寻常）在课程级别分配角色，而实际上并未注册课程本身。

However, both of the queries are flawed.

但是，这两个查询都有缺陷。

The first query could return duplicate results if the user was enroled in a course via more than one enrolment method (unusual, but possible). It also fails to take into account the following:

如果用户通过多个注册方法注册了课程（不寻常，但可能），第一个查询可能会返回重复的结果。它也没有考虑到以下几点：

• The enrolment plugin may be disabled at site level
• The enrolment plugin may be disabled at the course level (check for 'e.status = 0' to only find active enrolment plugins)
• The enrolment may be time-limited - the user's enrolment may have expired (check for 'ue.timeend = 0 OR ue.timeend > NOW()' to find only unexpired enrolments)

• 注册插件可能在站点级别被禁​​用
• 注册插件可能在课程级别被禁用（检查“e.status = 0”以仅找到活动的注册插件）
• 注册可能是有时间限制的 - 用户的注册可能已经过期（检查 'ue.timeend = 0 OR ue.timeend > NOW()' 以仅查找未过期的注册）

The second query assumes that the student role is id 5 (and also that there are no other roles, based on the student role, that are in use). I would normally either use an extra query to check the id of the 'student' role in the table 'mdl_role' and then use that value, or change the last couple of lines to the following:

第二个查询假设学生角色是 id 5（并且没有其他角色，基于学生角色，正在使用）。我通常会使用额外的查询来检查表 'mdl_role' 中的 'student' 角色的 id，然后使用该值，或者将最后几行更改为以下内容：

JOIN mdl_role r ON r.id = ra.roleid AND r.shortname = 'student'.

加入 mdl_role r ON r.id = ra.roleid AND r.shortname = 'student'。

The second query also fails to check the 'contextlevel' - it is possible to have a multiple contexts with the same instance id (as it is possible to have course id 5, course category id 5, user id 5, etc.) - so you need to check that the context found is a 'course' context (contextlevel = 50).

第二个查询也无法检查“contextlevel” - 可能有多个具有相同实例 ID 的上下文（因为可能有课程 ID 5、课程类别 ID 5、用户 ID 5 等） - 所以您需要检查找到的上下文是否是“课程”上下文（上下文级别 = 50）。

Neither query checks for suspended users or deleted users (although, in the case of deleted users, they should have been automatically unenroled from all courses at the point where they were deleted).

查询都不会检查暂停的用户或已删除的用户（尽管对于已删除的用户，他们应该在被删除时自动从所有课程中取消注册）。

A fully complete solution (possibly overly complex for most situations) would combine both queries together to check the user was enroled and assigned the role of student and not suspended:

一个完全完整的解决方案（在大多数情况下可能过于复杂）将两个查询结合在一起以检查用户是否已注册并分配了学生角色，而不是暂停：

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

(Note I haven't double-checked that query extensively - it runs, but you would need to carefully cross-reference against actual enrolments to check I hadn't missed anything).

（请注意，我没有广泛地仔细检查该查询 - 它运行，但您需要仔细对照实际注册情况进行交叉引用以检查我没有遗漏任何内容）。

回答by user3809024

The following code generates a list of all your courses together with how many students are enrolled in each. Useful to find out if you have any courses with no one enrolled.

以下代码生成所有课程的列表以及每个课程的注册学生人数。有助于了解您是否有任何没有人注册的课程。

My Answer :

我的答案 ：

SELECT cr.SHORTNAME, 
       cr.FULLNAME, 
       COUNT(ra.ID) AS enrolled 
FROM   `MDL_COURSE` cr 
       JOIN `MDL_CONTEXT` ct 
         ON ( ct.INSTANCEID = cr.ID ) 
       LEFT JOIN `MDL_ROLE_ASSIGNMENTS` ra 
              ON ( ra.CONTEXTID = ct.ID ) 
WHERE  ct.CONTEXTLEVEL = 50 
       AND ra.ROLEID = 5 
GROUP  BY cr.SHORTNAME, 
          cr.FULLNAME 
ORDER  BY `ENROLLED` ASC 

回答by SaidbakR

In the case of need the count of the enrolled students for a course. It may be achieved simply using the enrollment api. The secret key here is supplying withcapabilityparameter to the count_enrolled_users()function that only the Studentrole has. For example:

在需要的情况下，一门课程的注册学生人数。只需使用注册 api即可实现。这里的密钥是为只有角色拥有withcapability的count_enrolled_users()函数提供参数Student。例如：

$context = context_COURSE::instance($course->id);
count_enrolled_users($context,'mod/assignment:submit')

Here mod/assignment:submitis a capability that only student able to do, so the returned int number will not include other common roles such as Teachers enrolled in the course.

这mod/assignment:submit是一项只有学生才能执行的能力，因此返回的整数编号将不包括其他常见角色，例如在课程中注册的教师。

I have used the above code for Moodle 3.1 in theme renderer.phpto show the enrolled students count for each course in the courses list at the front page.

我在主题中使用了上面的 Moodle 3.1 代码，renderer.php以显示首页课程列表中每门课程的注册学生人数。

回答by Russell England

The first query will give you everyone regardless of their role - the table is used to store the type of enrolment - http://docs.moodle.org/26/en/Enrolment_plugins

第一个查询将为您提供每个人，无论他们的角色如何 - 该表用于存储注册类型 - http://docs.moodle.org/26/en/Enrolment_plugins

The second one will only give you students - so will be more useful.

第二个只会给你学生 - 所以会更有用。

They are the same results because only students have been assigned to the courses.

它们是相同的结果，因为只有学生被分配到课程中。

If you go to a course and enrol users. Then at the top of the popup window choose assign roles = teacher and enrol a user. So on the course you will now have student(s) and a teacher

如果您参加课程并注册用户。然后在弹出窗口的顶部选择分配角色 = 教师并注册一个用户。因此，在课程中，您现在将有学生和老师

Then re-run the queries, the second query will have fewer results because it will only have students.

然后重新运行查询，第二个查询的结果会更少，因为它只有学生。

回答by Yvonne Aburrow

If you want to get the courses that an individual user is signed up for...

如果您想获得单个用户注册的课程...

SELECT c.id, c.shortname, c.summary, c.idnumber  
FROM mdl_course c 
JOIN mdl_enrol en ON en.courseid = c.id 
JOIN mdl_user_enrolments ue ON ue.enrolid = en.id 
WHERE ue.userid = '12345'
AND  c.idnumber LIKE "blah%"

(The last line is optional and can be used to filter for courses of a particular type. Note that idnumberis an optional and manually editable field.)

（最后一行是可选的，可用于过滤特定类型的课程。请注意，这idnumber是一个可选且可手动编辑的字段。）