Database Tables
Tables required to fetch enrolled users depending on their course role.
mdl_user_enrolments
You can find API documentation for Enrolement API here: https://moodledev.io/docs/4.4/apis/subsystems/enrol
Table "public.mdl_user_enrolments"
Column | Type | Collation | Nullable | Default
--------------+--------+-----------+----------+-------------------------------------------------
id | bigint | | not null | nextval('mdl_user_enrolments_id_seq'::regclass)
status | bigint | | not null | 0
enrolid | bigint | | not null |
userid | bigint | | not null |
timestart | bigint | | not null | 0
timeend | bigint | | not null | 2147483647
modifierid | bigint | | not null | 0
timecreated | bigint | | not null | 0
timemodified | bigint | | not null | 0
Indexes:
"mdl_userenro_id_pk" PRIMARY KEY, btree (id)
"mdl_userenro_enr_ix" btree (enrolid)
"mdl_userenro_enruse_uix" UNIQUE, btree (enrolid, userid)
"mdl_userenro_mod_ix" btree (modifierid)
"mdl_userenro_use_ix" btree (userid)mdl_role
Table "public.mdl_role"
Column | Type | Collation | Nullable | Default
-------------+------------------------+-----------+----------+--------------------------------------
id | bigint | | not null | nextval('mdl_role_id_seq'::regclass)
name | character varying(255) | | not null | ''::character varying
shortname | character varying(100) | | not null | ''::character varying
description | text | | not null |
sortorder | bigint | | not null | 0
archetype | character varying(30) | | not null | ''::character varying
Indexes:
"mdl_role_id_pk" PRIMARY KEY, btree (id)
"mdl_role_sho_uix" UNIQUE, btree (shortname)
"mdl_role_sor_uix" UNIQUE, btree (sortorder)mdl_role_assignment
Table "public.mdl_role_assignments"
Column | Type | Collation | Nullable | Default
--------------+------------------------+-----------+----------+--------------------------------------------------
id | bigint | | not null | nextval('mdl_role_assignments_id_seq'::regclass)
roleid | bigint | | not null | 0
contextid | bigint | | not null | 0
userid | bigint | | not null | 0
timemodified | bigint | | not null | 0
modifierid | bigint | | not null | 0
component | character varying(100) | | not null | ''::character varying
itemid | bigint | | not null | 0
sortorder | bigint | | not null | 0
Indexes:
"mdl_roleassi_id_pk" PRIMARY KEY, btree (id)
"mdl_roleassi_comiteuse_ix" btree (component, itemid, userid)
"mdl_roleassi_con_ix" btree (contextid)
"mdl_roleassi_rol_ix" btree (roleid)
"mdl_roleassi_rolcon_ix" btree (roleid, contextid)
"mdl_roleassi_sor_ix" btree (sortorder)
"mdl_roleassi_use_ix" btree (userid)
"mdl_roleassi_useconrol_ix" btree (userid, contextid, roleid)mdl_context
Table "public.mdl_context"
Column | Type | Collation | Nullable | Default
--------------+------------------------+-----------+----------+-----------------------------------------
id | bigint | | not null | nextval('mdl_context_id_seq'::regclass)
contextlevel | bigint | | not null | 0
instanceid | bigint | | not null | 0
path | character varying(255) | | |
depth | smallint | | not null | 0
locked | smallint | | not null | 0
Indexes:
"mdl_cont_id_pk" PRIMARY KEY, btree (id)
"mdl_cont_conins_uix" UNIQUE, btree (contextlevel, instanceid)
"mdl_cont_ins_ix" btree (instanceid)
"mdl_cont_pat_ix" btree (path)
"mdl_cont_pat_ix_pattern" btree (path varchar_pattern_ops)mdl_user
Column | Type | Collation | Nullable | Default
-------------------+------------------------+-----------+----------+--------------------------------------
id | bigint | | not null | nextval('mdl_user_id_seq'::regclass)
auth | character varying(20) | | not null | 'manual'::character varying
confirmed | smallint | | not null | 0
policyagreed | smallint | | not null | 0
deleted | smallint | | not null | 0
suspended | smallint | | not null | 0
mnethostid | bigint | | not null | 0
username | character varying(100) | | not null | ''::character varying
password | character varying(255) | | not null | ''::character varying
idnumber | character varying(255) | | not null | ''::character varying
firstname | character varying(100) | | not null | ''::character varying
lastname | character varying(100) | | not null | ''::character varying
email | character varying(100) | | not null | ''::character varying
emailstop | smallint | | not null | 0
phone1 | character varying(20) | | not null | ''::character varying
phone2 | character varying(20) | | not null | ''::character varying
institution | character varying(255) | | not null | ''::character varying
department | character varying(255) | | not null | ''::character varying
address | character varying(255) | | not null | ''::character varying
city | character varying(120) | | not null | ''::character varying
country | character varying(2) | | not null | ''::character varying
lang | character varying(30) | | not null | 'en'::character varying
calendartype | character varying(30) | | not null | 'gregorian'::character varying
theme | character varying(50) | | not null | ''::character varying
timezone | character varying(100) | | not null | '99'::character varying
firstaccess | bigint | | not null | 0
lastaccess | bigint | | not null | 0
lastlogin | bigint | | not null | 0
currentlogin | bigint | | not null | 0
lastip | character varying(45) | | not null | ''::character varying
secret | character varying(15) | | not null | ''::character varying
picture | bigint | | not null | 0
description | text | | |
descriptionformat | smallint | | not null | 1
mailformat | smallint | | not null | 1
maildigest | smallint | | not null | 0
maildisplay | smallint | | not null | 2
autosubscribe | smallint | | not null | 1
trackforums | smallint | | not null | 0
timecreated | bigint | | not null | 0
timemodified | bigint | | not null | 0
trustbitmask | bigint | | not null | 0
imagealt | character varying(255) | | |
lastnamephonetic | character varying(255) | | |
firstnamephonetic | character varying(255) | | |
middlename | character varying(255) | | |
alternatename | character varying(255) | | |
moodlenetprofile | character varying(255) | | |
Indexes:
"mdl_user_id_pk" PRIMARY KEY, btree (id)
"mdl_user_alt_ix" btree (alternatename)
"mdl_user_aut_ix" btree (auth)
"mdl_user_cit_ix" btree (city)
"mdl_user_con_ix" btree (confirmed)
"mdl_user_cou_ix" btree (country)
"mdl_user_del_ix" btree (deleted)
"mdl_user_ema_ix" btree (email)
"mdl_user_fir2_ix" btree (firstnamephonetic)
"mdl_user_fir_ix" btree (firstname)
"mdl_user_idn_ix" btree (idnumber)
"mdl_user_las2_ix" btree (lastaccess)
"mdl_user_las3_ix" btree (lastnamephonetic)
"mdl_user_las_ix" btree (lastname)
"mdl_user_mid_ix" btree (middlename)
"mdl_user_mneuse_uix" UNIQUE, btree (mnethostid, username)Query über die Tabellen
Nachfolgendes Beispiel berücksichtigt Enrolment-Status im Kurs aktiv (status=0), außerdem generell ob User suspended oder deleted ist. Der Eintrag 0 ist in beiden Fällen ok. contextlevel 50 ist der Wert für den Kurkontext (siehe https://moodledev.io/docs/4.4/apis/subsystems/roles#context)
SELECT u.firstname, u.lastname, u.email, ra.roleid, r.shortname, e.status FROM mdl_user u JOIN mdl_role_assignments ra ON u.id=ra.userid JOIN mdl_user_enrolments e ON e.userid=u.id JOIN mdl_role r ON ra.roleid=r.id JOIN mdl_context c ON ra.contextid=c.id WHERE c.contextlevel=50 AND instanceid=2 AND r.id=5 AND e.status=0 AND u.deleted=0 AND u.suspended = 0;