adding custom profile fields as columns to a report about user completions

adding custom profile fields as columns to a report about user completions

by tim st.clair -
Number of replies: 2
Picture of Plugin developers

I'm trying to create a report that has user information, completion, and custom profile fields data as headings.

I can select users joined to their course completion records fine, it's something like

select u.email Email, u.firstname GivenName, u.lastname Surname, s.fullname Course, case when c.`timestarted` > 0 then from_unixtime(c.`timestarted`, '%Y %M %D %h:%i:%s') else 'Never' End Started, case when c.`timecompleted` > 0 then from_unixtime(c.`timecompleted`, '%Y %M %D %h:%i:%s') else 'Never' end Completed, c.*
from mdl_user u left outer join mdl_course_completions c
on u.id = c.`userid`
inner join mdl_course s on c.`course` = s.`id`

I've also figured out how to make my user profile fields appear as columns on a [separate] select statement

select
max(if(f.shortname = 'RegType',i.data, NULL)) AS RegistrationType,
max(if(f.shortname = 'REGnum',i.data, NULL)) AS RegistrationNumber,
max(if(f.shortname = 'GradYear',i.data, NULL)) AS GraduationYear,
max(if(f.shortname = 'Address',i.data, NULL)) AS Address,
max(if(f.shortname = 'DOB1', DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), INTERVAL + i.data SECOND), '%D %M %Y'), NULL)) AS DOB,
max(if(f.shortname = 'gender',i.data, NULL)) AS Gender
from (
	(mdl_user_info_data i join mdl_user_info_field f on i.fieldid = f.id join mdl_user u on i.userid = u.id)
) group by i.`userid`

but I can't seem to figure out how to take the columns both selects and join them together so the report has all those column from both queries (given some users may not have any custom profile fields defined, I still want those users to show). Any pointers?

Average of ratings: -
In reply to tim st.clair

Re: adding custom profile fields as columns to a report about user completions

by Mik O'Leary -

Hi Tim,

to add the user info data as columns, you have to do a subquery for each field so it returns a single value that matches the user field shortname for that column and the user ID for that row.

Here is the code:

SELECT

   u.email Email,

   u.firstname GivenName,

   u.lastname Surname,

   s.fullname Course,

   (select i.data

      from mdl_user_info_data i 

      join mdl_user_info_field f on i.fieldid = f.id

      WHERE i.userid = u.id

      AND f.shortname = 'RegType') RegistrationType,

  (select i.data

      from mdl_user_info_data i 

      join mdl_user_info_field f on i.fieldid = f.id

      WHERE i.userid = u.id

      AND f.shortname = 'REGnum') RegistrationNumber,

  (select i.data

      from mdl_user_info_data i 

      join mdl_user_info_field f on i.fieldid = f.id

      WHERE i.userid = u.id

      AND f.shortname = 'GradYear') GraduationYear,

  (select i.data

      from mdl_user_info_data i 

      join mdl_user_info_field f on i.fieldid = f.id

      WHERE i.userid = u.id

      AND f.shortname = 'Address') Address,

  (select DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(0), INTERVAL + i.data SECOND), '%D %M %Y')

      from mdl_user_info_data i 

      join mdl_user_info_field f on i.fieldid = f.id

      WHERE i.userid = u.id

      AND f.shortname = 'DOB1') DOB,

  (select i.data

      from mdl_user_info_data i 

      join mdl_user_info_field f on i.fieldid = f.id

      WHERE i.userid = u.id

      AND f.shortname = 'gender') Gender,


   CASE

      WHEN

         c.`timestarted` > 0 

      THEN

         from_unixtime(c.`timestarted`, '%Y %M %D %h:%i:%s') 

      ELSE

         'Never' 

   END

   Started, 

   CASE

      WHEN

         c.`timecompleted` > 0 

      THEN

         from_unixtime(c.`timecompleted`, '%Y %M %D %h:%i:%s') 

      ELSE

         'Never' 

   END

   Completed, c.* 

FROM

   mdl_user u 

   LEFT OUTER JOIN

      mdl_course_completions c 

      ON u.id = c.`userid` 

   INNER JOIN

      mdl_course s 

      ON c.`course` = s.`id`


Hope this works on your system.

Mik

Average of ratings: -
In reply to Mik O'Leary

Re: adding custom profile fields as columns to a report about user completions

by tim st.clair -
Picture of Plugin developers
Aah thanks that is so much neater.


I thought I'd managed to join my query (using the max(if(.. notation onto the query by selecting the fields, inner joining the user_info_data as an aliased subquery. (e.g. select from tablename inner join (subquery) aliasname on tablename.field = aliasname.field). This seems to optimise better but the result size I'm talking about is trivial.


select (fields) from users u inner join (select max(if(... etc) from (({user_info_data} i join {user_info_field} f on i.fieldid = f.id join {user} u on i.userid = u.id)) group by i.`userid`) q on q.userid = u.`id` left outer join completion c on c.userid = u.id (etc)

I like the neat readable structure of your solution better than my mess. Also it turns out that my subquery isn't matching users who don't have custom profile field values set, where as your solution does!

Thanks for your input!

Average of ratings: -