Hello -
I am attempting to write a SQL script that will show all students who have not yet completed a particular course. My exposure to SQL is very limited. I have been searching through the community forums and have not come across such a topic.
Does anyone have any ideas / suggestions of where I should begin in order to begin working on such a report? Any help or suggestions would be very much appreciated. Thanks.
Have you visited the ad-hoc contributed reports?
Hi Rick -
Thanks for the response. I did stumble into the link you mentioned. While it does seem like a useful resource, the specific idea of a report showing students who have not completed courses is not there.
It could be that such a report (students who have not completed courses) is something that Moodle isn't capable of. I am still familiarizing myself with Moodle as well.
If you do think of some kind of work around to gather this kind of information then feel free to let me know Thanks again for the help.
No such report. Yes, a query could be built ... a rather complicated one I would think.
Am not a SQL query ace so don't have one to share, but rather, something one could do using the 'work to work' track (eg. gotta do some other work to get what's desired! :\)
Let's say there is a course called ABC's of AC
The course is using completion tracking and the culminating
activity, etc. is a Certificate.
All other activity/content in the course is required before student can access cert.
Go to Reports -> Logs
You first have to set that search to the course - ABC's of AC
You then can choose the Cert activity - as per example above that's the cert.
"Simple Certificate: ABC's of Air Conditioning Certificate"
And search.
In the table shown, one will see a column for: Description.
Data for Description is like:
"The user with id '3524' updated the completion state for the course module with id '1624' for the user with id '3524'."
On the same page/table, there is an option to download the data as - pick your poison.
Moodle doesn't know what it doesn't know ... just like humans in this example.
But you have what it does know and can 'figure out' what it *didn't* report.
Now you have who completed ... and need to compare a list of all users in the course
with the data just acquired to find out who hasn't acquired that cert.
Ok, know it's not ideal ... etc. , etc. ... like I said ... 'work to work' :|
'spirit of sharing', Ken
Good Morning Ken -
Thanks for breaking down this concept for me in Moodle terms. I will attempt to put together what you outlined and see where that gets me this afternoon. Appreciate the help.
Ken has given you some guidance.
The question that you have to answer is "What is it in your courses that designates that a student has completed the course?"
In my courses, all students complete the course when the end of course date occurs on a regular calendar. After that date, all assignment dates have expired, and students get the grade that they earned. The course is over. However, my courses are at a university that has defined semesters.
Good Morning Rick -
I'm using Moodle on a corporate level, and hosting simple certification courses. They are made up of slideshows followed by a quick quiz. I am implementing the slideshows and quizzes via the plugin called H5P. Completion tracking / recording the data works well, but it would be nice to be able to run a query to show employees who have no taken particular certification courses.
Partially helpful.
In words, your current query seems to be "show me all students in my moodle who have not attempted all quizzes in these x certification courses." Or might it be "show me all students in my moodle who have not attempted the ABC quiz in the X course, the DEF quiz in the Y course, (etc.)"
My point is that you have to think like a (SQL) database programmer and be very specific about what you are asking. Also, I am not sure how the H5P plugin works in the database, and how it communicates results. When someone takes the quiz, is a grade put into the Moodle grade book?
I would not be the one to develop this query. I am only trying to help define exactly what you mean so that someone else might be able to help.
Hi Rick -
I am in this same situation again, having to attempt to go over this topic. I still have not found an efficient way to run reports of users who have not completed a course. Since I created this post I have been exporting the students enrolled in a course to Excel, and cross referencing that with the users who have completed the course via the grade book. It's time consuming and far from ideal.
A user completes this course by completing a quiz. When the quiz is complete they get a grade, which then populates the Course Total. What I need to do is find a way to pull the names of all enrolled users who do not have anything populated to the Course Total field.
I have been struggling to find any means of doing this.
I am still confused.
Are you using the Moodle grade book? What are you cross-referencing?
If "Course Total" is the Moodle grade book, and you are looking for it being blank, when you export the grade book to Excel, might you just sort this by Grade Total?
Or are you looking at final grade across many courses?
I see what you're saying about exporting the the grade book and sorting it by Course Total (with what is blank). That's certainly a solution.
I was just hoping there would be a quicker, more efficient way to show such a report. I have been attempting to use the Configurable Reports plugin, and trying to generate a SQL report. So far it has been very complicated and difficult.
The query below is the one that I use to show me all students and their final grades. I actually have this in my Configurable Reports. I will give you the CR (prefix_) form of this query. You might need to add one more WHERE clause to filter on gg.finalgrade. Notice that I am also filtering on final grade IS NOT NULL. Maybe you would want "IS NULL" to find students who do not yet have a final grade. In my case, if the student had received a grade on any item in the grade book, they will have a final grade. If you are using Activity Completion, you would need to add something in the query to filter on it.
Well, this is a start. I began this by looking at some of the contributed queries first, and then modified it. I cannot guarantee that it is 100% accurate, but it works well for my needs.
SELECT DISTINCT c.shortname AS CourseName,
u.firstname AS FirstName,
u.lastname AS LastName,
u.email AS Email,
gg.finalgrade AS FinalGrade,
c.idnumber AS CourseIDNum,
gi.courseid AS CourseNumber,
gg.userid AS StudentID,
3 AS moodleversion
FROM prefix_grade_items gi
JOIN prefix_grade_grades gg ON gi.id = gg.itemid
JOIN prefix_course c ON c.id = gi.courseid
JOIN prefix_user u ON u.id = gg.userid
JOIN prefix_context ct ON ct.instanceid = gi.courseid
JOIN prefix_role_assignments ra ON ra.userid = gg.userid
JOIN prefix_user_enrolments ue ON u.id = ue.userid
JOIN prefix_enrol e ON e.courseid = gi.courseid
WHERE gi.itemtype = "course"
AND gg.finalgrade IS NOT NULL
AND u.suspended = 0
AND u.deleted = 0
AND ct.contextlevel = 50
AND ra.roleid = 5
AND ct.id = ra.contextid
AND ue.status = 0
AND e.status = 0
AND e.id = ue.enrolid
ORDER BY c.idnumber DESC,
u.lastname,
u.firstname
Hey Rick -
This is really helpful. I really appreciate it. With my limited knowledge of SQL, this gives me something to begin to build off of.
Quick question - is there a way to make this report specific to one course? I tried typing in a line reading:
AND c.shortname like "%Refresher%"
The above line gives me no results displayed on the report. I do know that there are some grades in the grade book that are null though.
Again though, thank you for the help.
Yes, you are correct for "the course."
You should be able to verify the SQL query by going into the Moodle course and seeing for yourself it the SQL query seems to be correct.
A quick test would be:
select *
from mdl_course
where shortname = "the courses short name"
If you get nothing, your course's shortname must be wrong or the course doesn't exist.
I am not sure where you are experimenting. I tend to do this using phpMyAdmin. In this tool, I can try as many queries as I like. Then, when my query is working, I put it into Configurable Reports.
Good Morning Rick -
I've been attempting to work through it further. So far I have:
SELECT c.fullname AS CourseName, u.firstname AS FirstName, u.lastname AS LastName, u.email AS Email, gg.finalgrade AS FinalGrade
FROM prefix_grade_items gi
JOIN prefix_grade_grades gg ON gi.id = gg.itemid
JOIN prefix_course c ON c.id = gi.courseid
JOIN prefix_user u ON u.id = gg.userid
JOIN prefix_context ct ON ct.instanceid = gi.courseid
JOIN prefix_role_assignments ra ON ra.userid = gg.userid
JOIN prefix_user_enrolments ue ON u.id = ue.userid
JOIN prefix_enrol e ON e.courseid = gi.courseid
WHERE gi.itemtype = "course"
AND gg.finalgrade IS NULL
AND c.fullname like "%Refresh%"
ORDER BY c.idnumber DESC,
u.lastname,
u.firstname
Something to note - I changed c.shortname to c.fullname when referring to a course. With the line "AND c.fullname like "%Refresh%"" I get No Records Found when running the report. Clearly I have something wrong with my syntax here.
With the SELECT * mdl_course test you mentioned, I was able to pull the Refresher course via the fullname.
Do you see any red flags in my above report?
Just want to follow up with where I am at so far. It seems like in my case the course final grade is not the measurement I need for this particular course. I need to explore how to pull Activity Completion and/or Course Total as being NULL.
From what I can tell the closes thing I can get to Activity Completion in the database is the timecompleted column in the mdl_course_completions table.
As of now I cannot find where Course Total is stored in the Moodle database.
The current query I have is:
SELECT Distinct c.fullname, u.firstname, u.lastname, u.email, gc.timecompleted
FROM prefix_course as c, prefix_user as u, prefix_grade_items gi, prefix_course_completions gc
WHERE u.id = gc.userid
AND gi.itemtype = "course"
AND gc.timecompleted IS NULL
AND c.fullname LIKE "%Refresher%"
ORDER BY c.idnumber DESC,
u.lastname,
u.firstname
The issue here is that some of the names in the report have in fact completed the course. Meaning there are a handful of students in this list who have 100% Activity Completion as well as Course Total of 80 - 100.
I don't use activity completion, so I can't help with that. But my guess is that there is a table, or tables, that contain that information. I see a number of tables in my Moodle 3.6 that have "completion" in their names.
The only help that I can be is to suggest to you to search the contributed reports.
hi can you suggest me sql query for displaying grade category screenshot is attached
