0

I'm currently working on a configurable report and I may need to get the date of the users' first access to a Course.

I can easily get it via logstore_standard_log with the following query:

SELECT FROM_UNIXTIME(timecreated) AS firstCourseAccess
FROM mdl_logstore_standard_log
WHERE eventname = "\core\event\course_viewed"
AND action = "viewed"
AND target = "course"
AND userid = 3
ORDER BY timecreated
LIMIT 1

However, there is the possibility of purging this logs.

Is there another way to get this record without adding a new field/table on the database?

Any help/idea is very much appreciated. Cheers!

Goma
  • 2,018
  • 1
  • 10
  • 19

1 Answers1

0

You could use the user enrolments table which records the time enrolled - but it might not be the first time a user accessed the course, they could have been enrolled long before access. Also a user could be unenrolled and then enrolled again, so you will need to add some logic.

SELECT e.courseid, ue.userid, MIN(ue.timestart) AS timeenrolled
FROM mdl_user_enrolments ue
JOIN mdl_enrol e ON e.id = ue.enrolid
GROUP BY e.courseid, ue.userid

You could also use course completions if its enabled for the course - but this is reset if the completions is reset for the user and/or the course

SELECT cc.course, cc.userid, cc.timestarted
FROM mdl_course_completions cc
WHERE cc.timestarted > 10 /* COMPLETION_STATUS_NOTYETSTARTED */
Russell England
  • 9,436
  • 1
  • 27
  • 41