Configurable reports block (plugin)

Calling a custom profile field in SQL report

 
 
John Lowe
Calling a custom profile field in SQL report
 

I am using a custom SQL report to gather information on course completion for administrators. I need to call a custom profile field I created that was named School or Site. It has a shortname of "site". How can I add this selection to the SQL I am using. The query I am using is listed below:

SELECT u.firstname AS First, u. lastname AS Last, c.fullname AS Course,  
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
),'%Y-%m-%d')
AS completed
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
WHERE c.enablecompletion = 1
ORDER BY c.fullname, u.lastname

 
Average of ratings: -
Picture of Emma Richardson
Re: Calling a custom profile field in SQL report
Group Particularly helpful Moodlers

The way to call a custom field is profile_field_fieldname

 
Average of ratings: -
John Lowe
Re: Calling a custom profile field in SQL report
 

Thanks Emma, but I am fairly new to this. Can you help me with what table this data is stored in and how to format the SQL query? Also, how do I know how the fieldname is stored in the database? Is it the shortname?

 
Average of ratings: -
Randy Thornton
Re: Calling a custom profile field in SQL report
Group Documentation writersGroup Particularly helpful Moodlers

 

John

This will show you how:

SELECT u.username, uif.shortname, uid.data
FROM prefix_user AS u
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id

I borrowed this logic from a query over in ad-hoc queries page - http://docs.moodle.org/25/en/ad-hoc_contributed_reports#Full_User_info_.28for_deleted_users.29

 

The field definition is stored in _user_info_field. Shortname and name are text.

The answer to the custom field by a user is stored in _ user_info_data.data. It is text for the text types, and also returns text for pull down boxes. A checkbox field returns a 0 or 1. DateTime types return the usual Unix epoch time stamp.

 

 

 
Average of ratings:Useful (1)
John Lowe
Re: Calling a custom profile field in SQL report
 

Thanks Randy. This helped quite a bit. The code above works well and I adapted it to show just the site data listed as "site". But as I said, I am quite new to this and now I need help combining the results I was getting with this new information. How do I combine the query for my course completion status with the fields I want and add the data for each user's site?

 
Average of ratings: -
John Lowe
Re: Calling a custom profile field in SQL report
 

I got it to work thanks to your help!

Here is the query in final form:

SELECT u.firstname AS first, u.lastname AS last, u.email AS email, uid.data AS site, c.fullname AS course,
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
),'%Y-%m-%d')
AS completed
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
WHERE c.enablecompletion = 1
ORDER BY uid.data, c.fullname, u.lastname, u.firstname

 

 
Average of ratings: -
Randy Thornton
Re: Calling a custom profile field in SQL report
Group Documentation writersGroup Particularly helpful Moodlers

Great.

That will work as long as you have only one custom profile field. There can be multiple rows in user_info_data for the same user id, so if you have more than one, it will return them ALL in the "site" column. (See first attachment.)

If you want to limit to only one, you would need that join with user_info_field and then use something in the WHERE clause to limit it. Assuming the custom profile field has a shortname of 'site' then:


SELECT u.firstname AS first, u.lastname AS last, u.email AS email, uif.shortname AS fieldname, uid.data AS fielddata, c.fullname AS course,
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
),'%Y-%m-%d') AS completed

FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id
WHERE c.enablecompletion = 1 AND WHERE uif.shortname = 'site'
ORDER BY uid.data, c.fullname, u.lastname, u.firstname

That gives the data in your case, but will work when you add more fields too smile

To see all the fields with their names and data, just take out that addition to the WHERE clause and you will see them all... see attached example.

 

 

 



 
Average of ratings: -
Picture of John S
Re: Calling a custom profile field in SQL report
 

Randy, is that SQL script for postgresql

 
Average of ratings: -
Randy Thornton
Re: Calling a custom profile field in SQL report
Group Documentation writersGroup Particularly helpful Moodlers

I did it on MySQL.

I believe that DATE_FORMAT is peculiar to MySQL & you need something else for Postgres.

See: http://stackoverflow.com/questions/12052705/date-format-in-postgresql

 
Average of ratings: -
Picture of Ahniwa Ferrari
Re: Calling a custom profile field in SQL report
 

Randy, or anyone else who can help,

I'm trying to run this report (SQL Query below) but to include the custom user profile fields. I was able to get the shortnames through your first suggestion, but when I try and run the query as you suggested it above (changing 'site' to 'state') I get an error. Can you help? Here are the custom profile fields:

Custom Profile Fields


Here is the SQL query for the report I would like to run. Essentially all completed courses in the past 30 days. This query works fine, as is, but doesn't work when I try and add the uif / uid info:

SELECT u.username, u.email, u.firstname, u.lastname, c.shortname,  
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
),'%Y-%m-%d') 
AS completed
FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
WHERE c.enablecompletion = 1 and p.timecompleted >(UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -30 DAY)))
ORDER BY u.username


Any help is appreciated!


 
Average of ratings: -
Picture of Ahniwa Ferrari
Re: Calling a custom profile field in SQL report
 

I got this figured out and even managed (with some help: https://moodle.org/mod/forum/discuss.php?d=263979) to get multiple custom profile fields showing up in their own columns. Sharing the current, working query here in case it is of use to others.


SELECT u.lastname, u.firstname, u.email, uid.data AS state, uid2.data AS librarytype, uid3.data AS workplace, c.shortname,
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
),'%Y-%m-%d') 
AS completed

FROM prefix_course_completions AS p
JOIN prefix_course AS c ON p.course = c.id
JOIN prefix_user AS u ON p.userid = u.id
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_field AS uif ON uid.fieldid = uif.id
JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
JOIN prefix_user_info_field AS uif2 ON uid2.fieldid = uif2.id
JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id
JOIN prefix_user_info_field AS uif3 ON uid3.fieldid = uif3.id
WHERE c.enablecompletion = 1  and uif.shortname = 'state' and uif2.shortname = 'librarytype' and uif3.shortname = 'workplace' and p.timecompleted >(UNIX_TIMESTAMP(DATE_ADD(CURDATE(),INTERVAL -30 DAY)))
ORDER BY u.lastname
 
Average of ratings: -
Picture of José Augusto Moraes
Re: Calling a custom profile field in SQL report
 

Hi John;

I use this simple code:

global $USER;
$string = $USER->profile['fieldname'];

Just change the 'fieldname' to the custom field you want to display.

Hope that helps!

 
Average of ratings: -