Count number of distinct learners and teachers enrolled per category (including all its sub categories)

Count number of distinct learners and teachers enrolled per category (including all its sub categories)

by Ricardo Caiado -
Number of replies: 5
Picture of Particularly helpful Moodlers
Moodle 3.10+
PostgreSQL 12

What´s wrong?

https://docs.moodle.org/311/en/ad-hoc_contributed_reports#Count_number_of_distinct_learners_and_teachers_enrolled_per_category_.28including_all_its_sub_categories.29

SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) AS teachers
FROM prefix_course AS c #, prefix_course_categories AS cats
LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS lra ON lra.contextid = ctx.id
JOIN prefix_role_assignments AS tra ON tra.contextid = ctx.id
JOIN prefix_course_categories AS cats ON c.category = cats.id
WHERE c.category = cats.id
AND (
cats.path LIKE '%/CATEGORYID/%' #Replace CATEGORYID WITH the category id you want TO COUNT (eg: 80)
OR cats.path LIKE '%/CATEGORYID'
)
AND lra.roleid=5
AND tra.roleid=3

------------------------------

Error when executing the query: Error reading from database ERROR: syntax error at or near "#" LINE 2: FROM mdl_course AS c #, mdl_course_categories AS cats ^ SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) AS teachers FROM mdl_course AS c #, mdl_course_categories AS cats LEFT JOIN mdl_context AS ctx ON c.id = ctx.instanceid JOIN mdl_role_assignments AS lra ON lra.contextid = ctx.id JOIN mdl_role_assignments AS tra ON tra.contextid = ctx.id JOIN mdl_course_categories AS cats ON c.category = cats.id WHERE c.category = cats.id AND ( cats.path LIKE '%/CATEGORYID/%' #Replace CATEGORYID WITH the category id you want TO COUNT (eg: 80) OR cats.path LIKE '%/CATEGORYID' ) AND lra.roleid=5 AND tra.roleid=3 LIMIT 2 [array egg]

-------------------------------
Average of ratings: -
In reply to Ricardo Caiado

Re: Count number of distinct learners and teachers enrolled per category (including all its sub categories)

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators
Hi Ricardo,

I don't know PostgreSQL, but reading your error message, i think you can just, on lines containing # (meant to be comment), delete from the # to the end of the line.

HTH,
Séverin
Average of ratings: Useful (1)
In reply to Séverin Terrier

Re: Count number of distinct learners and teachers enrolled per category (including all its sub categories)

by Ricardo Caiado -
Picture of Particularly helpful Moodlers
Thanks Séverin!

Now I got this....

SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) AS teachers
FROM prefix_course AS c, prefix_course_categories AS cats
LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS lra ON lra.contextid = ctx.id
JOIN prefix_role_assignments AS tra ON tra.contextid = ctx.id
JOIN prefix_course_categories AS cats ON c.category = cats.id
WHERE c.category = cats.id
AND (
cats.path LIKE '%/357/%'
OR cats.path LIKE '%/357'
)
AND lra.roleid=5
AND tra.roleid=3

---------------------------------

ERROR:  invalid reference to FROM-clause entry for table "c"
LINE 3: LEFT JOIN mdl_context AS ctx ON c.id = ctx.instanceid
                                                                        ^
HINT:  There is an entry for table "c", but it cannot be referenced from this part of the query.
SQL state: 42P01
Character: 171
Average of ratings: -
In reply to Ricardo Caiado

Re: Count number of distinct learners and teachers enrolled per category (including all its sub categories)

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators
Hi Ricardo,

If i'm right, the link beetween course table and course_categories table is already done in the latest join line, and shouldn't be done (again) in the WHERE clause.

So your WHERE clause should be :
WHERE lra.roleid=5 AND tra.roleid=3
AND (cats.path LIKE '%/357/%' OR cats.path LIKE '%/357')
HTH,
Séverin
Average of ratings: Useful (1)
In reply to Séverin Terrier

Re: Count number of distinct learners and teachers enrolled per category (including all its sub categories)

by Ricardo Caiado -
Picture of Particularly helpful Moodlers
Hi Séverin,

SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) AS teachers
FROM prefix_course AS c, prefix_course_categories AS cats
LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments AS lra ON lra.contextid = ctx.id
JOIN prefix_role_assignments AS tra ON tra.contextid = ctx.id
JOIN prefix_course_categories AS cats ON c.category = cats.id
WHERE lra.roleid=5 AND tra.roleid=3
AND (cats.path LIKE '%/357/%' OR cats.path LIKE '%/357')

----------------------------------

ERROR:  invalid reference to FROM-clause entry for table "c"
LINE 3: LEFT JOIN mdl_context AS ctx ON c.id = ctx.instanceid
                                                                        ^
HINT:  There is an entry for table "c", but it cannot be referenced from this part of the query.
SQL state: 42P01
Character: 171
Average of ratings: -
In reply to Ricardo Caiado

Re: Count number of distinct learners and teachers enrolled per category (including all its sub categories)

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators

Hi Ricardo,

In fact, after some search (and if i've well understood), it seems that PostgreSQL is less permissive than MySQL, and doesn't allow mixing explicit and implicit joins.

And you didn't follow my first advice of deleting from # to the end of the lines where they existed (but i'm not sure it's the real problem). I also see you declare table twice, with both implicit and explicit relation.

If think (don't have PostgreSQL to test) that the query should better be :

SELECT COUNT(DISTINCT lra.userid) AS learners,
       COUNT(DISTINCT tra.userid) AS teachers
FROM prefix_course            AS c
LEFT JOIN prefix_context      AS ctx  ON c.id          = ctx.instanceid
JOIN prefix_role_assignments  AS lra  ON lra.contextid = ctx.id
JOIN prefix_role_assignments  AS tra  ON tra.contextid = ctx.id
JOIN prefix_course_categories AS cats ON c.category    = cats.id
WHERE lra.roleid=5
  AND tra.roleid=3
  AND (cats.path LIKE '%/357/%' OR cats.path LIKE '%/357')

HTH,
Séverin

Average of ratings: -