How to include custom user profile field in SQL?

How to include custom user profile field in SQL?

by Marinda Fourie -
Number of replies: 3

Hi there, I am new to Moodle and inexperienced in SQL. 

I found this SQL which works fine, instead, I would like to change the 'city' column to a custom user profile which I have created called: jobtitle.
_______________________________________________________

SELECT

user2.firstname AS Firstname,

user2.lastname AS Lastname,

user2.email AS Email,

user2.city AS City,

course.fullname AS Course

,(SELECT shortname FROM prefix_role WHERE id=en.roleid) AS ROLE

,(SELECT name FROM prefix_role WHERE id=en.roleid) AS RoleName

 

FROM prefix_course AS course 

JOIN prefix_enrol AS en ON en.courseid = course.id

JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id

JOIN prefix_user AS user2 ON ue.userid = user2.id

_____________________________________________________________________________

I have tried to change the line:
user2.city AS City,
to: 
user2.profile_field_jobtitle AS Jobtitle


But that didn't work. 

Thank you very much for your help. 

Average of ratings: -
In reply to Marinda Fourie

Re: How to include custom user profile field in SQL?

by Emma Richardson -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Plugin developers

You need to look through the database to find where custom fields are stored.  Custom profile fields are not stored in the moodle user table.

Average of ratings: -
In reply to Marinda Fourie

Re: How to include custom user profile field in SQL?

by Tim Obezuk -

Hi Marinda,

If your Moodle site is running on MySQL, you can achieve this by adding a sub query to your SELECT clause.

For example:

SELECT firstname,
       lastname,
       (SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'jobtitle' AND UID.userid = U.id) as 'jobtitle'
FROM prefix_user U

This query assumes the Profile Field's shortname is "jobtitle".

Your query can be successfully updated as follows:

SELECT
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.city AS City,
(SELECT UID.data FROM prefix_user_info_field UIF, prefix_user_info_data UID WHERE UID.fieldid = UIF.id AND UIF.shortname = 'jobtitle' AND UID.userid = user2.id) as 'jobtitle',
course.fullname AS Course
,(SELECT shortname FROM prefix_role WHERE id=en.roleid) AS ROLE
,(SELECT name FROM prefix_role WHERE id=en.roleid) AS RoleName
FROM prefix_course AS course 
JOIN prefix_enrol AS en ON en.courseid = course.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
JOIN prefix_user AS user2 ON ue.userid = user2.id
Average of ratings: Useful (2)
In reply to Tim Obezuk

Re: How to include custom user profile field in SQL?

by Marinda Fourie -

Thank you very much Tim!!!

It works like a charm! 

Much appreciated. smile

Average of ratings: -