Useful SQL Queries?

Re: Useful SQL Queries?

by Darko Miletić -
Number of replies: 3

Your query looks really strange. Here is the cleaned up version:

    SELECT u.id AS userid,
           u.firstname,
           u.lastname,
           FROM_UNIXTIME (l.time) AS rvisit,
           c.fullname AS rcourse,
           agg.days,
           agg.numdates,
           agg.numcourses,
           agg.numlogs,
           r.name AS role
      FROM mdl_log l
      JOIN mdl_user             u  ON l.userid = u.id AND u.deleted = 0
      JOIN mdl_role_assignments ra ON ra.userid = u.id
      JOIN mdl_context          ct ON ct.id = ra.contextid AND ct.contextlevel = 50
      JOIN mdl_course           c  ON c.id = ct.instanceid AND l.course = c.id AND c.format <> 'site'
      JOIN mdl_role             r  ON r.id = ra.roleid
      JOIN (
            SELECT
                   30 AS days,
                   l1.userid,
                   MAX(l1.time) AS maxtime,
                   COUNT(DISTINCT DATE(FROM_UNIXTIME(l1.time))) AS numdates,
                   COUNT(DISTINCT l1.course) AS numcourses,
                   COUNT(*) AS numlogs
              FROM mdl_log    l1
              JOIN mdl_course c  ON l1.course = c.id
             WHERE
                   l1.time > UNIX_TIMESTAMP(CURRENT_TIMESTAMP - INTERVAL 30 DAY)
                   AND
                   c.format <> 'site'
          GROUP BY l1.userid
           ) agg ON l.userid = agg.userid
     WHERE l.time = agg.maxtime
  GROUP BY userid
  ORDER BY l.time DESC



Average of ratings: Useful (1)
In reply to Darko Miletić

Re: Useful SQL Queries?

by usma virtual -

Hello Darko,


You rock,'re right my query was something strange, thank you cooperation.


For my part when you require any support, do not hesitate to ask.


I will mention you have this step consultations being, where it shows me the number of students per course, but I can not show this amount by group within each course.


this is my query:


SELECT c.id AS Course_ID,c.shortname AS Course_Shortname,c.fullname AS Course_fullname, r.name AS Role,COUNT(u.id) AS Count_Active_Users

FROM mdl_role_assignments ra

LEFT JOIN mdl_context cx ON (ra.contextid = cx.id)

LEFT JOIN mdl_course c ON (cx.contextlevel = 50 AND cx.instanceid = c.id)

LEFT JOIN mdl_user u ON (ra.userid = u.id)

LEFT JOIN mdl_role r ON (ra.roleid = r.id)

WHERE cx.contextlevel = 50

AND c.visible = 1

AND u.deleted = 0

AND u.suspended = 0

GROUP BY r.name, c.id

ORDER BY c.id ASC

******************************************************

I appreciate the support.

Have a nice day.

Average of ratings: -
In reply to Darko Miletić

Re: Useful SQL Queries?

by usma virtual -

Here is the working query, it is obvious that you mostly guess your SQL. Definitely recommend to get yourself some book or take a course to learn it better. It is wrong to guess anything.

*******************************************************

I understand what you mention, actually I work in the consultations being, but could not identify this point.

I thank infinamente contribution to resolve this query, if at some point you require any support whatever is not hesitate to mention it.

have an excellent day.

Average of ratings: -
In reply to usma virtual

Re: Useful SQL Queries?

by Rey T -

Can anyone help me in writing a query to retrieve students who have viewed a particular course home page? e.g. (moodle.com/course/view.php?id=2) . The following query displays everyone who have viewed the course but I need to narrow down the result to only those with student role in that course. (I'm using moodle 2.8.1)


SELECT `id`, `eventname`, `target` , `action`, `crud` , `contextid`, `contextlevel`, `contextinstanceid` , `userid` , `courseid` , FROM_UNIXTIME(`timecreated`) AS DATEE 

FROM `mdl_logstore_standard_log` 

WHERE  `courseid` = 2 AND `target` = 'course' 

AND `crud` = 'r'

ORDER BY DATEE desc

Average of ratings: -