Site Wide - Count Course Completions

Site Wide - Count Course Completions

由Shirley Gregorczyk發表於
Number of replies: 11
Particularly helpful Moodlers的相片

Dear Experts,

I have this handy query - borrowed for the ad-hoc contributions page. Each result is slightly overstated, as it includes my completion - when I launched the course and tested the course behaviors. How do I exclude completions that were done by non-enrolled students? I have tried inserting the %%FILTER_COURSEENROLLEDSTUDENTS%% after the WHERE statement - which results in a query error. 

Truly puzzled, Shirley


SELECT DISTINCT

c.fullname AS Course, 

COUNT(cp.timecompleted) AS Completions

FROM prefix_course_completions AS cp

JOIN prefix_course AS c ON cp.course = c.id

JOIN prefix_user AS u ON cp.userid = u.id

WHERE (c.visible = 1)

AND (u.deleted = 0) AND (u.suspended = 0)

GROUP BY c.id

ORDER BY c.fullname

評比平均分數： -
In reply to Shirley Gregorczyk

Re: Site Wide - Count Course Completions

由Randy Thornton發表於
You will need to add some logic that checks whether the user is in the course and if so do they have the student role. You could add this logic to the where clause or you could integrate the joins in the main section.

There are two main ways to do this: go through the user_enrolments table or go through the role_assignments table. Normally, either will work for students since they will always have both an enrolment and a role assignment. There are examples of both methods over in the ad-hoc reports. Just search for the table names to see some ways to do it.

Here's one simple way to do it for role assignments:

SELECT u.username,
c.shortname
FROM prefix_course as c
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 5
AND ctx.instanceid = c.id


That just shows you every student in every course. The role number for the default Student role is 5. If you have a custom role instead, you would need to use that role id number. I documented the standard role ids here: https://docs.moodle.org/400/en/ad-hoc_contributed_reports#Role_id_numbers
評比平均分數：Useful (2)
In reply to Randy Thornton

Re: Site Wide - Count Course Completions

由Shirley Gregorczyk發表於
Particularly helpful Moodlers的相片
So I tried this both ways - using the role and using the enrollments as subqueries. It is still a puzzle -

With role assignment - I get the same results as if I didn't add the role subquery?
SELECT DISTINCT
c.fullname AS Course,
COUNT(cp.timecompleted) AS Completions
FROM prefix_course_completions AS cp
JOIN prefix_course AS c ON cp.course = c.id
JOIN prefix_user AS u ON cp.userid = u.id
WHERE (c.visible = 1)
AND (u.deleted = 0) AND (u.suspended = 0)
GROUP BY c.id
ORDER BY c.fullname
IN (SELECT ( ra.userid ) FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id)


With Enrollments - I get the same results as if I didn't add the enrollment subquery?
SELECT DISTINCT
c.fullname AS Course,
COUNT(cp.timecompleted) AS Completions
FROM prefix_course_completions AS cp
JOIN prefix_course AS c ON cp.course = c.id
JOIN prefix_user AS u ON cp.userid = u.id
WHERE (c.visible = 1)
AND (u.deleted = 0) AND (u.suspended = 0)
GROUP BY c.id
ORDER BY c.fullname
,(SELECT DISTINCT course
FROM prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
WHERE (c.visible = 1)
GROUP BY c.id
ORDER BY c.fullname)
評比平均分數： -
In reply to Shirley Gregorczyk

Re: Site Wide - Count Course Completions

由Randy Thornton發表於
In the first one, your IN statement is added to the end and so has no effect: it's just being ignored It needs to go before the GROUP BY and ORDER BY and should be part of the WHERE clause.

The second one is similar as the sub-select at the end is also just tacked on.

What you are aiming for, based on the fact you only want the results if the user is a Student in the course, would be to think about selecting only those particular USERs. Your first one with the role_assignments is pretty close to what you want to do. Try something like WHERE stuff AND u.id IN (SELECT....only those with role assigment 5 in c.id)
評比平均分數：Useful (1)
In reply to Randy Thornton

Re: Site Wide - Count Course Completions

由Shirley Gregorczyk發表於
Particularly helpful Moodlers的相片
Thank you - I made the suggested changes on both query methods.
Neither query had errors but they also produced no results.
I need to noodle on on this.
Thank you for providing me with useful feedback.
評比平均分數： -
In reply to Shirley Gregorczyk

Re: Site Wide - Count Course Completions

由Randy Thornton發表於
What's the latest query look like?
評比平均分數： -
In reply to Randy Thornton

Re: Site Wide - Count Course Completions

由Shirley Gregorczyk發表於
Particularly helpful Moodlers的相片
using the enroll logic - Results = 0

SELECT DISTINCT
c.fullname AS Course,
COUNT(cp.timecompleted) AS Completions
FROM prefix_course_completions AS cp
JOIN prefix_course AS c ON cp.course = c.id
JOIN prefix_user AS u ON cp.userid = u.id
IN (SELECT DISTINCT course
FROM prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
WHERE en.id = u.id
AND (c.visible = 1)
AND (u.deleted = 0) AND (u.suspended = 0) )
ORDER BY c.fullname

Using role logic - Results = 0

SELECT DISTINCT
c.fullname AS Course,
COUNT(cp.timecompleted) AS Completions
FROM prefix_course_completions AS cp
JOIN prefix_course AS c ON cp.course = c.id
JOIN prefix_user AS u ON cp.userid = u.id
WHERE (c.visible = 1)
AND (u.deleted = 0) AND (u.suspended = 0)
IN (SELECT DISTINCT ( ra.userid ) FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id)
ORDER BY c.fullname
評比平均分數： -
In reply to Shirley Gregorczyk

Re: Site Wide - Count Course Completions

由Randy Thornton發表於
IN is a comparison operator. In both examples, you aren't really comparing anything.

So, the second one means

WHERE
one thing is this
AND another thing is that
AND yet another thing is that
what thing? is IN (that)

So, what is the value you are checking for that is IN the selection set?

u.id IN(...) would be okay.
評比平均分數：Useful (1)
In reply to Randy Thornton

Re: Site Wide - Count Course Completions

由Randy Thornton發表於

Personally, I would avoid the sub select here anyway. In queries like this I tend to just use JOINs to do it because I know I am not going to be excluding anything. Generally (there's always exceptions I suppose), a JOIN is faster than a sub-select (so they say).


SELECT
c.fullname AS Course,
COUNT(cp.timecompleted) AS Completions

FROM prefix_course_completions AS cp
JOIN prefix_course AS c ON cp.course = c.id
JOIN prefix_user AS u ON cp.userid = u.id
JOIN prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id AND ctx.instanceid = c.id
WHERE c.visible = 1
AND u.deleted = 0
AND u.suspended = 0
AND ra.roleid = 5

GROUP BY c.id
ORDER BY c.fullname, u.username

評比平均分數： -
In reply to Randy Thornton

Re: Site Wide - Count Course Completions

由Shirley Gregorczyk發表於
Particularly helpful Moodlers的相片
Thank you -
The one thing I have learned, besides I cannot write a decent query, is that using a Join is faster - the sub-select does take much longer to execute. Of course I had is spinning in a loop.
Thank you for coaching me through his.
評比平均分數： -
In reply to Shirley Gregorczyk

Re: Site Wide - Count Course Completions

由Randy Thornton發表於
No problem 微笑

The real SQL gurus (and gurinis) say as a general rule that JOIN is faster than a sub-selects. It has to do with how the interpreter builds the actual query internally. But sometimes a sub-select or a sub-query is the only way to do things. Or can save a lot of code. And it can be good as a first approximation too.

For IN(), I have read that it is basically a shortcut for OR where for example u.id IN(1,2,3) is the same logic as u.id = 1 OR u.id = 2 OR u.id = 3. So u.id IN (SELECT....) is the same as u.id = ORs with all the results of the subquery.

Also, in general I find that just checking role_assignments for finding teachers and students is all you need. You don't usually need to also check the enrolments tables. Normal course users will have both but the role_assignments is where the information is stored about which role(s) they have. So unless your query is just counting all the enrollments regardless of the roles, you are going to need role_assignments anyway.
評比平均分數：Useful (2)
In reply to Randy Thornton

Re: Site Wide - Count Course Completions

由Shirley Gregorczyk發表於
Particularly helpful Moodlers的相片
When I got 0 results - I figured I was asked was missing / excluding the data I wanted -
This completely verifies I was clueless -
評比平均分數： -