last access to course

last access to course

Bởi Mariano Bichara -
Số lượng các câu trả lời: 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

Trung bình điểm đánh giá: -
Để phản hồi tới Mariano Bichara

Re: last access to course

Bởi 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)
Trung bình điểm đánh giá:Useful (1)
Để phản hồi tới David Saylor

Re: last access to course

Bởi 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)

Trung bình điểm đánh giá: -
Để phản hồi tới Mariano Bichara

Re: last access to course

Bởi Mariano Bichara -

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

Trung bình điểm đánh giá: -
Để phản hồi tới Mariano Bichara

Re: last access to course

Bởi 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
Trung bình điểm đánh giá:Useful (2)