SQL Help

SQL Help

by Fred Nipper -
Number of replies: 7

Hi all,

I am using the Configurable reports block, and I am trying to get a report for my users to view that will show them a transcript (report) of the courses that they have completed and along with their score and date taken.  I have not had any success so far in figuring out the SQL coding for how to do this and was wondering if anyone could help me out.  I have been able to get a report to show that lists all of the courses they are enrolled in, but I want a specific one to show only the courses they have completed and the date they completed it.

Any help would be greatly appreciated.

Average of ratings: -
In reply to Fred Nipper

Re: SQL Help

by Brad Smith -

It looks like the information you're looking for is in the prefix_course_completions table. Are you familiar with doing joins in SQL? Just use one to integrate that table into your query and you should have everything you need. If you're still stuck, post the query you have and maybe someone can suggest a way to tweak it. 

Average of ratings: -
In reply to Brad Smith

Re: SQL Help

by Fred Nipper -

I know some SQL, but I'm far from very good at it.  I found the code below, and it accomplishes part of what I want by only pulling complete courses, however it pulls everyone sitewide.  I need to edit it to only show the current user as well as the date.  Hopefully an SQL expert will be able to point me in the right direction.

SELECT u.firstname AS 'Firstname', u.lastname AS 'Lastname', c.shortname AS Course, ROUND( gg.finalgrade, 0 ) AS 'Final grade'
FROM prefix_course c
JOIN prefix_context ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments ra ON ra.contextid = ctx.id
JOIN prefix_user u ON u.id = ra.userid
JOIN prefix_grade_grades gg ON gg.userid = u.id
JOIN prefix_grade_items gi ON gi.id = gg.itemid
JOIN prefix_course_categories cc ON cc.id = c.category
WHERE gi.courseid = c.id
AND gi.itemtype = 'course'
ANd u.deleted = 0
ORDER BY gg.finalgrade DESC

Average of ratings: -
In reply to Fred Nipper

Re: SQL Help

by Brad Smith -

EDIT: The news is not as good as I'd hoped. See next post for why.

Good news! The filtering you want to do doesn't actually require any SQL! If you've got a query that gives you what you want for all users, just do the following:

  1. Create a report with the query that gets the information you want for all users 
  2. Click on Conditions
  3. Select Current User from the Add: dropdown
  4. Type c1 in the Condition box
  5. Click Update

That should be it!

Average of ratings: -
In reply to Fred Nipper

Re: SQL Help

by Brad Smith -

Argh. Well, I take it back. It looks like you can't create conditions like that on a custom SQL report.

In that case, unless someone knows a way to insert the current user into the SQL statement I'm not sure what you want to do is actually possible. sad

If you can insert variables like that into the query, though, I'd love to know about it! Can anyone confirm/deny for sure?

Average of ratings: -
In reply to Fred Nipper

Re: SQL Help

by Brad Smith -

A HA! I'm glad you asked this question, because it finally got me off my but and digging through the code enough to answer this question.

Turns out you can use '%%USERID%%' to refer to the current user's ID, so if you change your query like so...

SELECT u.firstname AS 'Firstname', u.lastname AS 'Lastname', c.shortname AS Course, ROUND( gg.finalgrade, 0 ) AS 'Final grade'
FROM prefix_course c
JOIN prefix_context ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments ra ON ra.contextid = ctx.id
JOIN prefix_user u ON u.id = ra.userid
JOIN prefix_grade_grades gg ON gg.userid = u.id
JOIN prefix_grade_items gi ON gi.id = gg.itemid
JOIN prefix_course_categories cc ON cc.id = c.category
WHERE gi.courseid = c.id
AND gi.itemtype = 'course'
AND u.deleted = 0
AND u.id = %%USERID%%
ORDER BY gg.finalgrade DESC

then it will be limited to the current user. 

HOWEVER...

I don't think this is actually the query you want. This just gets the users' individual grades. Gimme a sec and I'll try to come up with something more like what I think you want...

Average of ratings: -
In reply to Brad Smith

Re: SQL Help

by Brad Smith -

See if this is closer to what you want. I am assuming that you are using the course completion feature (http://docs.moodle.org/24/en/Course_completion). I don't actually have any students who have completed anything yet, so I can only test so far, but it should get you started, at least. 

select 
	u.firstname, 
	u.lastname, 
	cr.fullname, 
	from_unixtime(cm.timecompleted) 
from  
	prefix_user u 
	join prefix_user_enrolments eu on u.id = eu.userid 
	join prefix_enrol ec on eu.enrolid = ec.id 
	join prefix_course cr on cr.id = ec.courseid 
	join prefix_course_completions cm on cr.id = cm.course and u.id = cm.userid 
where 
	cm.timecompleted is not NULL
	and u.id = %%USERID%%
order by
	cm.timecompleted

Good luck!

Average of ratings: -
In reply to Brad Smith

Re: SQL Help

by Fred Nipper -

Brad,

You rock!!  The code above worked, but it wouldn't pull any records.  However, the previous post by adding the and u.id = %%USERID%% worked like a dream (just without the completed date).  I never thought to use the and statement. 

I'm rolling with that one without the completed dates for now. 

Thanks so much for all your help!!

Fred

Average of ratings: -