last access to course

last access to course

de către Mariano Bichara-
Număr de răspunsuri: 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

Media aprecierilor: -
Ca răspuns la Mariano Bichara

Re: last access to course

de către 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)
Media aprecierilor:Useful (1)
Ca răspuns la David Saylor

Re: last access to course

de către 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)

Media aprecierilor: -
Ca răspuns la Mariano Bichara

Re: last access to course

de către Mariano Bichara-

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

Media aprecierilor: -
Ca răspuns la Mariano Bichara

Re: last access to course

de către 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
Media aprecierilor:Useful (2)
Ca răspuns la David Saylor

Re: last access to course

de către Mariano Bichara-
Thanks a lot David, it works like a charm. I'm sure this repo will help a lot of people, thanks again.
Media aprecierilor: -