last access to course

last access to course

by Mariano Bichara -
Number of replies: 5

Hello, I want a report that gives information about last time users accessed courses, as it can be seen in user's profiles. Last login to moodle seems to work fine, but I cannot get last access to each course an user is enrolled to. Maybe I'm in the wrong table? JOIN it's no correct? Here is my query, I've bolded what seems not to be working.
Thanks a lot in advance.

  SELECT u.username AS UserName
      ,rn.shortname AS RoleName
      ,c.fullname AS CourseName
      ,u.lastname AS APELLIDO
      ,u.firstname AS NOMBRE
      ,u.email AS CORREO
      ,c.category AS CategoriaID
      ,c.shortname AS NomCorto
      ,c.timecreated AS CreadoEl
      ,u.currentlogin AS ULTIMOLOGIN
      ,u.firstaccess AS primerACCESO
      ,ul.timeaccess AS ULTIMOACCCURSO
     
 FROM prefix_role_assignments AS r
   JOIN prefix_user AS u on r.userid = u.id
   JOIN prefix_role AS rn on r.roleid = rn.id
   JOIN prefix_context AS ctx on r.contextid = ctx.id
   JOIN prefix_course AS c on ctx.instanceid = c.id
   JOIN prefix_user_lastaccess AS ul on ul.courseid = c.id

Average of ratings: -
In reply to Mariano Bichara

Re: last access to course

by David Saylor -
The user_lastaccess looks fine, but your context join needs context level. Courses are context level 50. Could add a WHERE ctx.contextlevel = 50 or add it to the join (JOIN prefix_context ctx ON r.contextid = ctx.id AND ctx.contextlevel = 50)
Average of ratings:Useful (1)
In reply to David Saylor

Re: last access to course

by Mariano Bichara -

Thank you David! Afer I've asked here for help, I've improved my query as to prune results to certain categories and not hit the 5000 limit. It works ok until I add "timeaccess", it shows repeated users and hit the limit. I've added context as you suggest, but it didn't work either. Here are my actual query, any other suggestions appreciated.

SELECT u.username AS UserName
      ,rn.shortname AS RoleName
      ,c.fullname AS CourseName
      ,u.lastname AS APELLIDO
      ,u.firstname AS NOMBRE
      ,u.email AS CORREO
      ,c.category AS CategoriaID
      ,c.summary AS DESCRIPCION
      ,c.id AS CursoID
      ,c.shortname AS NomCorto
      ,c.timecreated AS CreadoEl
      ,u.currentlogin AS ultimoLOGIN
      ,u.firstaccess AS primerACCESO
      ,ul.timeaccess AS ULTIMOACCaCURSO

FROM prefix_role_assignments AS r
   JOIN prefix_user AS u on r.userid = u.id
   JOIN prefix_role AS rn on r.roleid = rn.id
   JOIN prefix_context AS ctx on r.contextid = ctx.id
   JOIN prefix_course AS c on ctx.instanceid = c.id
   JOIN prefix_user_lastaccess AS ul on ul.courseid = u.id AND ctx.contextlevel = 50

WHERE
    (rn.shortname = 'student' AND c.category=6) OR
    (rn.shortname = 'student' AND c.category=7) OR
    (rn.shortname = 'student' AND c.category=8) OR
    (rn.shortname = 'student' AND c.category=9) OR
    (rn.shortname = 'student' AND c.category=11)OR
    (rn.shortname = 'student' AND c.category=12)OR
    (rn.shortname = 'student' AND c.category=13)OR
    (rn.shortname = 'student' AND c.category=14)

Average of ratings: -
In reply to Mariano Bichara

Re: last access to course

by Mariano Bichara -

Bumpity. I'm stuck. Any help is appreciated.

Average of ratings: -
In reply to Mariano Bichara

Re: last access to course

by David Saylor -
Ahh, I missed it in your original post but you also need to join user lastaccess on userid. So something like:
JOIN prefix_user_lastaccess AS ul ON ul.courseid = c.id AND ul.userid = u.id
Average of ratings:Useful (2)
In reply to David Saylor

Re: last access to course

by Mariano Bichara -
Thanks a lot David, it works like a charm. I'm sure this repo will help a lot of people, thanks again.
Average of ratings: -