why some users do not appear in mdl_user_info_data in Moodle?

Question

I am getting students added to course using a query but i noticed 2 of them do not appear( even if they are active and listed as participants) checking inside tables i noticed they do not appear in mdl_user_info_data. How can i prevent this? or whats the reason they dont were added to this table

This is my query:

SELECT u.id,u.username,u.firstname,u.lastname,u.email, b.data
        FROM mdl_user u,mdl_role_assignments r, mdl_user_info_data b
        WHERE u.id=r.userid
        AND u.id=b.userid
        AND r.roleid=5
        AND r.contextid = 'somecontextId'
        ORDER BY u.email ASC;

score 0 · Accepted Answer · answered Mar 04 '22 at 06:58

The table mdl_user_info_data holds the values for custom user profile fields (that are defined in mdl_user_info_field).

If a user's profile has not been edited since a particular custom user profile field has been created, then there will not be an associated mdl_user_info_data record for them.

Note that if there is more than one custom user field defined, there can be more than one mdl_user_info_data field - so your query could return more than one record per user.

You probably want to rewrite your query to LEFT JOIN with mdl_user_info_data. You probably also want to LEFT JOIN with mdl_user_info_field to identify which of the custom user profile fields it relates to.

Also note that your query makes a number of assumptions that may not always be true - if your query is running inside Moodle code, then you should use {user_info_data} instead of the 'mdl_' prefix, as that prefix can be changed. Hard-coding roleid 5 for 'student' can also fail on some sites (although it is usually the case).

why some users do not appear in mdl_user_info_data in Moodle?

1 Answers1