Calling a custom profile field in SQL report

Calling a custom profile field in SQL report

by John Lowe -
Number of replies: 20

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: -
In reply to John Lowe

Re: Calling a custom profile field in SQL report

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

The way to call a custom field is profile_field_fieldname

Average of ratings: -
In reply to Emma Richardson

Re: Calling a custom profile field in SQL report

by John Lowe -

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: -
In reply to John Lowe

Re: Calling a custom profile field in SQL report

by Randy Thornton -
Picture of Documentation writers

 

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 (2)
In reply to Randy Thornton

Re: Calling a custom profile field in SQL report

by John Lowe -

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: -
In reply to Randy Thornton

Re: Calling a custom profile field in SQL report

by John Lowe -

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: -
In reply to John Lowe

Re: Calling a custom profile field in SQL report

by Randy Thornton -
Picture of Documentation writers

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.

 

 

 

Attachment screenshot_577.png
Attachment screenshot_578.png
Average of ratings: -
In reply to Randy Thornton

Re: Calling a custom profile field in SQL report

by John S -

Randy, is that SQL script for postgresql? 

Average of ratings: -
In reply to Randy Thornton

Re: Calling a custom profile field in SQL report

by Ahniwa Ferrari -

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: -
In reply to Ahniwa Ferrari

Re: Calling a custom profile field in SQL report

by Ahniwa Ferrari -

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:Useful (1)
In reply to Ahniwa Ferrari

Re: Calling a custom profile field in SQL report

by Keith Murray -

This is fantastic Ahniwa!  Thanks so much for posting it has saved this "novice" a lot of time.

One question if you don't mind, I have used a modified version below for course completion with my custom profile fields and I was trying to tweak it to get scorm completion results but don't know exactly what to modify in order to accomplish that.  I tried piecing together a standard scorm report using this core but get constant errors.  Thanks in advance!!


SELECT u.lastname, u.firstname, u.email, uid.data AS comp, uid2.data AS post, uid3.data AS detachment, 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 = 'comp' and uif2.shortname = 'post' and uif3.shortname = 'detachment' and p.timecompleted 

Average of ratings: -
In reply to Keith Murray

Re: Calling a custom profile field in SQL report

by Melanie Scott -
Picture of Particularly helpful Moodlers

Yowza!  A sql report on scorm data!  I can't tell you how but I can tell you it will NOT be pretty, even if you succeed.  And it might not be useful.  Scorm data (variable data, which included question answers) is all lumped in a comma separated field.  One (very small) example looks like this:

;~;VarQuestion_0006=True;VarQuestion_0005=False;VarQuestion_0004=True;VarQuestion_0003=True;VarQuestion_0002=Yes;VarQuestion_0001=My%20personal%20life%20is%20none%20of%20your%20business.%20;

This one doesn't actually have a test, just questions throughout.  If there was a test, there would be more variables.  It doesn't tell you which question is which (unless you've named the variable for the question and keep all that garbage in your head), what the other options were or what the right answer is.  I once created a crystal report (which include all sorts of stuff for formatting and prettifying) to pull Scorm data and added a bunch of crazy stuff to break up the variables in a per-line format, named the variables, etc and added formatting to replace the %20 with a space and it was still monumentally ugly.

Now, after being dreadfully discouraging, I'll ask.  What specifically are you looking to get out of scorm data?  Or do you just want a grade (that's possible and probably easy).

Average of ratings: -
In reply to Ahniwa Ferrari

Re: Calling a custom profile field in SQL report

by Diego Bronstein -

This code works, but if one of the fields has no data, the record does not show at all.


It requires all fields to have data in them. How can one make SQL show all records even with empty values?

Average of ratings: -
In reply to Diego Bronstein

Re: Calling a custom profile field in SQL report

by Randy Thornton -
Picture of Documentation writers

Diego,

Right, because Moodle does not go back and create rows for the data for already existing users when you create a new custom field. So there's no entry to return data from until the user adds something.

(On the other hand, if you add a new user, they do get any entry in the table for already defined and existing custom profile fields, and the data there is empty. That's the behavior in 3.1 anyway.)

This is a job for an OUTER JOIN. Use outer join in the query to include all users even if they have no data in those fields.

Here's the most basic way to do that:

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

This gives you the basics of what you seem to be looking for (see attachment).


Randy




Attachment screenshot_1689.jpg
Average of ratings: -
In reply to Ahniwa Ferrari

Re: Calling a custom profile field in SQL report

by Michele Costantini -

Hello,

i'm using this form, is possible show the user also if not all the rows are filled? Maybe show a error or NULL value in the missing row

Because now if is missing one or more rows in the user profile the script doesn't show the user,

Thanks

Michele

Average of ratings: -
In reply to John Lowe

Re: Calling a custom profile field in SQL report

by José Augusto Moraes -

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: -
In reply to José Augusto Moraes

Re: Calling a custom profile field in SQL report

by Federica Marra -

Dear all, I'm trying to run this sql in Moodle 2.8.5 but it gives me an error message back!

Could someone help me in writing one sql to display a custom user profile field called "Targetgroup" in the shortname?

Thanks! 

Average of ratings: -
In reply to Federica Marra

Re: Calling a custom profile field in SQL report

by Al Brocklehurst -

I got this working for Moodle 2.8+

Federica - if you are still having problems paste your sql and we'll look at it.

Average of ratings: -
In reply to José Augusto Moraes

Re: Calling a custom profile field in SQL report

by Matt Polaniecki -

Where would this go? At the top? I'm sorry I'm new to this.

Average of ratings: -
In reply to John Lowe

Re: Calling a custom profile field in SQL report

by Marc Sferaz -

This query will allow you to view the custom profile fields as columns in your ad-hoc report.  You'll need to modify the WHERE clause with the custom fields that you are trying to select on. Below is an example:


SELECT

      u.username,

      u.firstname,

      u.lastname,

      MAX(CASE WHEN f.shortname = 'PostalCode' THEN d.data ELSE '' END) AS PostalCode, 

      MAX(CASE WHEN f.shortname = 'Address' THEN d.data ELSE '' END) AS Address1,

      MAX(CASE WHEN f.shortname = 'Province' THEN d.data ELSE '' END) AS Province

FROM prefix_user u

      JOIN prefix_user_info_data d ON d.userid = u.id

      JOIN prefix_user_info_field f ON d.fieldid = f.id

      WHERE f.shortname IN ('Address', 'Province', 'PostalCode')  /* the shortname corresponds to the custom profile field label */

GROUP BY u.id

Average of ratings: -