Help to show all students in course, with groups, and whether or not they've completed

Help to show all students in course, with groups, and whether or not they've completed

by Mark Berthelemy -
Number of replies: 4

Hi all,

I'm trying to create a report which shows:

  • All the students in the current course
  • The groups those students belong to
  • Whether or not the students have completed the course
  • Several of the students' custom profile fields

The output I'm trying to get is something like:

Desired output
Username Country Custom profile field 1
(Organisation Type)		 Custom profile field 2
(Role Type)		 Course Group Completed (Y/N)
student1 UK Research institute Scientist Pest Management CABI Y
student1 UK Research institute Scientist Pest Management Researchers Y
student2 FR Government body Trainer Pest Management Researchers N

Actual output
Username Country Custom profile field 1
(Organisation Type)		 Custom profile field 2
(Role Type)		 Course Group Completed (Y/N)
student1 UK Research institute Scientist Pest Management CABI Y
student1 UK Research institute Scientist Pest Management Researchers Y
student1  UK Research institute Scientist Pest Management Researchers Y
student2 FR Government body Trainer Pest Management Researchers Y

I'm using the Certificate Issued (custom certificate plugin) as a proxy for completion, but happy to go with an alternative method.

A student may be in more than one group.

You can see in the actual output that I'm ending up with:

  • Duplicate lines
  • Everyone is showing as completed

The query I'm working with is shown below:

SELECT c.shortname AS Course, u.username AS 'Username', u.country AS 'Country', uid.data AS 'Organisation Type', uir.data AS 'Role Type', g.name AS Groupname,
	CASE WHEN i.userid IS NOT NULL # Should test to see whether the student has a certificate or not
	THEN 'Yes'
	ELSE "No"
	END
	AS "Has certificate"
FROM prefix_course AS c
# Select the certificate

JOIN prefix_customcert AS cc ON cc.course = c.id # Select the certificates that have been issued

JOIN prefix_customcert_issues AS i ON i.customcertid = cc.id
# Connect the user to the certificate issues

RIGHT JOIN prefix_user AS u ON i.userid = u.id # Why is this showing that everyone has a certificate?
# Show custom profile fields

JOIN prefix_user_info_data as uid ON uid.userid = u.id

JOIN prefix_user_info_field as uif1 ON (uid.fieldid = uif1.id AND uif1.shortname = 'OrganisationType')

JOIN prefix_user_info_data as uir ON uir.userid = u.id

JOIN prefix_user_info_field as uif2 ON (uir.fieldid = uif2.id AND uif2.shortname = 'RoleType')

JOIN prefix_groups_members AS m ON u.id = m.userid

JOIN prefix_groups AS g ON g.id = m.groupid

WHERE c.id = %%COURSEID%%

Any help gratefully received!

Thanks,

Mark
Average of ratings: -
In reply to Mark Berthelemy

Re: Help to show all students in course, with groups, and whether or not they've completed

by Randy Thornton -
Picture of Documentation writers

Off the top of my head, I think this issue appears to be that you are testing for a NULL for the Custom Certificate completion but also are using an INNER JOIN to join to the certificate issues. So, that is restricting the output to only the non-nulls there, and as a result everyone is a Yes.

I think a LEFT JOIN may be a way around that.
Average of ratings: -
In reply to Randy Thornton

Re: Help to show all students in course, with groups, and whether or not they've completed

by Mark Berthelemy -
Thanks Randy,
I changed the line: JOIN prefix_customcert_issues AS i ON i.customcertid = cc.id

I tried with both a LEFT and RIGHT join, but it made no difference. It still shows that everyone has a certificate.

Cheers,

Mark
Average of ratings: -
In reply to Mark Berthelemy

Re: Help to show all students in course, with groups, and whether or not they've completed

by Randy Thornton -
Picture of Documentation writers
Did you try changing both the cert tables to left joins?
Average of ratings: -
In reply to Mark Berthelemy

Re: Help to show all students in course, with groups, and whether or not they've completed

by Motasem Al Haj Ali -
Hi Mark,

I think Randy meant to check this part of the code,
CASE WHEN i.userid IS NOT NULL # Should test to see whether the student has a certificate or not
THEN 'Yes'
ELSE "No"
END
AS "Has certificate"
It is going to be always yes because the userid will not be null, you need to change this condition. I think the condition should be related to customcert or customcert_issue, you can try this

CASE WHEN i.userid = u.id # because the customcert_issues is the table which Stores each issue of a customcert
THEN 'Yes'
ELSE "No"
END
AS "Has certificate"

I hope that will help.

Regards
Mo
Average of ratings: -