Configurable reports block (plugin)

 
 
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 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: -