Adding a field from mdl_user_info_data to quiz results export

Adding a field from mdl_user_info_data to quiz results export

by Bernard Bailey -
Number of replies: 7
Hi developers,

I'm working with moodle 1.94. Our client runs a large primary manufacturing organisation with a number of sites.

To identify where users are located we have added an additional profile field called location in which we store the site name. This is stored in the mdl_user_info_data table.

We now require this site name to appear in the quiz results table.

I have been able to successfully add additional fields to the quiz results table and export them correctly where the field is from the mdl_user table.

However, when I join the mdl_user_info_data table I get the message "Nothing to display"


Here is the snippet for the data select from mod\quiz\report\overview\report.php

// Construct the SQL
$select = 'SELECT '.sql_concat('u.id', '\'#\'', $db->IfNull('qa.attempt', '0')).' AS uniqueid, ';
if ($qmsubselect) {
$select .=
"(CASE " .
" WHEN $qmsubselect THEN 1" .
" ELSE 0 " .
"END) AS gradedattempt, ";
}

$select .= 'qa.uniqueid AS attemptuniqueid, qa.id AS attempt, ' .
'u.id AS userid, u.idnumber, u.department, u.firstname, u.lastname, u.picture, u.imagealt, uid.data, ' .
'qa.sumgrades, qa.timefinish, qa.timestart, qa.timefinish - qa.timestart AS duration ';

// This part is the same for all cases - join users and quiz_attempts tables
$from = 'FROM '.$CFG->prefix.'user u ';
$from .= 'LEFT JOIN '.$CFG->prefix.'quiz_attempts qa ON qa.userid = u.id AND qa.quiz = '.$quiz->id;
// This line joins mdl_user_info_data for additional fields
$from .= 'LEFT JOIN '.$CFG->prefix.'user_info_data uid ON uid.userid = u.id AND uid.fieldid =2';



When I run this select in MySQL as below it returns the correct records and data

select qa.uniqueid AS attemptuniqueid , qa.id AS attempt, u.id AS userid, u.idnumber, u.department
, u.firstname, u.lastname, u.picture, u.imagealt, uid.data as 'Site Location', qa.sumgrades, qa.timefinish
, qa.timestart, qa.timefinish - qa.timestart AS duration
 FROM mdl_user u
LEFT JOIN mdl_quiz_attempts qa ON qa.userid = u.id
LEFT JOIN mdl_user_info_data uid ON uid.userid = u.id and uid.fieldid=2
where qa.quiz =60


1. Is the export function expanded in moodle 2.0 to handle requests like this?

2. What is the problem with my join that causes it to return "Nothing to display"?

3. Is the problem actually in the table displaying part of the code; in that I have an incorrect
reference to the new field data and this is what is causing the display to return "Nothing to display"

Cheers
Bernard

PS.. Is there any way to "watch" threads, rather than get whole "forum" updates?


Average of ratings: -
In reply to Bernard Bailey

Re: Adding a field from mdl_user_info_data to quiz results export

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
1. No. This is an increasingly common request, so we will need to address it one day, but there is just too much other stuff in Moodle 2.0 for us to contemplate adding this too.

2. If the JOIN works in MySQL, then it is very strange that it does not work in Moodle. Can I suggest
a. In the code, just above the get_records_sql call, output the SQL it is about to execute (print_object($select.$from.$where.$sort) should do that.)
b. Copy and paste that SQL into MySQL to test it.

3. The way to check this is to put in a print_object($attemtps) to see what PHP got back from the database, and to see whether you are accessing it correctly.


P.S. No, but there is a feature request in tracker.moodle.org that you can vote for.
Average of ratings: -
In reply to Tim Hunt

Re: Adding a field from mdl_user_info_data to quiz results export

by François Marier -
There is also a patch on the tracker for a similar feature: adding user info columns to the gradebook exports.

http://tracker.moodle.org/browse/MDL-17346

Might be useful to do it in a similar way...
Average of ratings: -
In reply to François Marier

Re: Adding a field from mdl_user_info_data to quiz results export [solved]

by Paweł Suwiński -
François Marier on 16 04 2009, 12:13 wrote:
> There is also a patch on the tracker for a similar feature: adding > user info columns to the gradebook exports.
>
> http://tracker.moodle.org/browse/MDL-17346
>
> Might be useful to do it in a similar way...

I did it this way. Here is the patch with solution: CONTRIB-2395.

Average of ratings: -
In reply to Tim Hunt

Re: Adding a field from mdl_user_info_data to quiz results export

by Bernard Bailey -
Hi Tim,

Many thanks for responding, I have found a solution to this issue. I had to add a space before the LEFT JOIN on the mdl_user_info_data table as in

$from .= ' LEFT JOIN '.$CFG->prefix.'user_info_data uid ON uid.userid = u.id AND uid.fieldid =2';

The reason for this was without the space, that section of the query looks like this:

ts qa ON qa.userid = u.id AND qa.quiz = 60LEFT JOIN user_info_data uid ON uid.userid = u.id AND uid.fieldid =2

That would result in a syntax error, since qa.quiz looks to be a numeric field testing against unquoted string data (60LEFT). Even assuming the syntax went through, the parser would consider this an INNER JOIN (since LEFT would no longer be considered a join modifier), which would eliminate any rows not matching the final table.
(Courtesy experts_exchange id: ID:24154378 Author:routinet Date:16/04/09 03:46 PM)

Cheers
Bernard


Average of ratings: -
In reply to Bernard Bailey

Re: Adding a field from mdl_user_info_data to quiz results export

by Bernard Bailey -
Hi Tim,

Perhaps if I put together how I achieved this in a private message for the solution to be sanitised; then this could be added into a further release for those interested?

Cheers
Bernard

Average of ratings: -
In reply to Bernard Bailey

Re: Adding a field from mdl_user_info_data to quiz results export

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
The trouble is, if we were going to add something like this to Moodle, would want a general system, with some admin settings to control it, and so on, and it would be good if all user reports in Moodle shared the same settings. So, suddenly you have a moderately big project.

In the mean time, I would suggest that the most useful thing you could do is to write a page in the developer documentation at Moodle docs, that explains to other developers how to make this sort of customisation to their site.
Average of ratings: -
In reply to Tim Hunt

Re: Adding a field from mdl_user_info_data to quiz results export

by Zachary Johnson -
Hello,

Can someone point me to instructions on installing this feature? This is a feature that I greatly desire.

Thanks,
-Zach
Average of ratings: -