3

In the query below, it is currently displaying user that is enrolled in courses. I would like the query to show non enrolled user also.

SELECT user.firstname AS Firstname, user.lastname AS Lastname, user.email AS Email, course.fullname AS Course

FROM mdl_course AS course JOIN mdl_enrol AS en ON en.courseid = course.id JOIN mdl_user_enrolments AS ue ON ue.enrolid = en.id JOIN mdl_user AS user ON ue.userid = user.id
Nishat Anjum Lea
  • 117
  • 12
Aaron Tan
  • 31
  • 2

1 Answers1

2

What you have to do is simple. You must first check for active enrollment methods because you don't want to get users from suspended or deactivated enrollments. These can be filtered out using the enrol table with the status column. If it's set to 0 the enrollment is active, 1 otherwise. These values are represented with contacts in the Moodle enrollment library lib/enrollib.php:

/** Course enrol instance enabled. (used in enrol->status) */
define('ENROL_INSTANCE_ENABLED', 0);

/** Course enrol instance disabled, user may enter course if other enrol instance enabled. (used in enrol->status)*/
define('ENROL_INSTANCE_DISABLED', 1);

/** User is active participant (used in user_enrolments->status)*/
define('ENROL_USER_ACTIVE', 0);

/** User participation in course is suspended (used in user_enrolments->status) */
define('ENROL_USER_SUSPENDED', 1);

You can use these constants with your custom Moodle queries to keep them understandable and clean.

Users enrollments are easy using the user_enrolments table which has the same status column indicating the status of the user enrollment status.

Now to answer your question we need to understand your request. In your query, you are listing all users enrolled (active/inactive) in all your courses. If you are looking to show the list of users per course and list the non-enrolled users you can do the following:

SELECT u.id, u.firstname as Firstname, u.lastname as Lastname, u.email as Email, ue.status as Enrolled
  FROM mdl_user u
  LEFT JOIN mdl_user_enrolments ue ON ue.userid = u.id
  JOIN mdl_enrol e ON e.id = ue.enrolid
   AND e.courseid = <your course id>

With this query, you will get all the users in your database and a column indicating if the user is enrolled (active/inactive) or not in your specified course. You can always use the CASE WHEN THEN ELSE to set custom values in the Enrolled column in your result or interpret them when they get them in your code.

If you want to list all the enrollments on your website and show the non-enrolled users you can do the same.

mohessaid
  • 390
  • 2
  • 14