Users connection by category

Users connection by category

by Gosia Kuc -
Number of replies: 1

Dear Moodlers,

I am completely debutant in the SQL codes.

But I learn fast. If I need help.

I will need to take out of my Moodle the report which contains :

the names of the users  (role user) who participated in the courses (ordered by categories ) in certain between April and June 2020.


I have found code like that but I don't manage to make it work. it says :



SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) AS teachers
FROM prefix_course AS c #, mdl_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 '%/2434/%' #Replace CATEGORYID WITH the category id you want TO COUNT (eg: 80)
    OR cats.path LIKE '%/2434'
    )
AND lra.roleid=5
AND tra.roleid=3


Can you help?

Thank you in advance,

Gosia

Average of ratings: -
In reply to Gosia Kuc

Re: Users connection by category

by David Saylor -
The error shown is coming from the comment "#, mdl_course_categories AS cats". The validation looks for explicit table prefixes and prevents them. If you delete from # to the end of the line, it should at least execute.

The where statement doesn't seem to help you unless you have specific requirements you haven't mentioned.

You'll also want to add an ORDER BY that references the category name if you want them ordered by categories.

To restrict by the dates, I would probably recommend joining the user_enrolments table to get their enrolment timestart and timeend to grab only the ones in that range.
Average of ratings: -