Activity report grouped by "groups" in course | Query help

Activity report grouped by "groups" in course | Query help

by Nadirshah Ferozepurwalla -
Number of replies: 4

Activity report in Moodle for a course shows Activity, Views and Last access. We require this data to be broken down into groups present in the course. How many views were made to an activity by the unique users present in a particular group.

For this we have managed to write the below query. The problem here is that the users which are not present in any course groups are not accounted for! why is that? there is a Left join which should instruct SQL to also pick records which are not present in any groups.

Please help,


SELECT ug.groupid, grps.name, slog.contextinstanceid as cmid, COUNT('x') AS numviews, COUNT(DISTINCT slog.userid) AS distinctusers, MAX(slog.timecreated) AS lasttime 

FROM prefix_logstore_standard_log AS slog

LEFT JOIN prefix_groups_members AS ug ON ug.userid = slog.userid 

 INNER JOIN prefix_groups AS grps ON grps.id=ug.groupid 

WHERE grps.courseid = 2156 AND slog.courseid = 2156 AND slog.anonymous = 0 AND slog.crud = 'r' AND slog.contextlevel = 70 

GROUP BY slog.contextinstanceid, ug.groupid

Average of ratings: -
In reply to Nadirshah Ferozepurwalla

Re: Activity report grouped by "groups" in course | Query help

by Randy Thornton -
Picture of Documentation writers
Because the LEFT join is saying include unmatched rows from left table, which is the groups_members table. But an enrolled user who is not in any group will not be in that table to start with; no row exist for them.

If you did this on the enrol_instances table, then you could would get all the enrol users whether in a group or not. You are using the group course id to match the specific course here. If you redid this based on enrollment then combined with group, then you could get all the users in groups or not. That would be a rather different query though. Or you could try flipping it around to use the log table as the left table to get the users and/or look at a right join to the log.
Average of ratings: -
In reply to Randy Thornton

Re: Activity report grouped by "groups" in course | Query help

by Nadirshah Ferozepurwalla -

The table to the left is the log store table, or am I getting my left and right sides incorrect? 

Average of ratings: -
In reply to Nadirshah Ferozepurwalla

Re: Activity report grouped by "groups" in course | Query help

by Randy Thornton -
Picture of Documentation writers
Nadirshah,

You are quite correct. My mistake there. I misread that. The log table is on the left in the FROM.

I think what is happening though is that your filter is setting a condition with grps.courseid = 2156 that basically is turning the left join back to an inner join since it is forcing a condition to include only users in groups. Again, those users who have no group membership would be excluded by that. I would suspect that if you just had the slog.courseid = 2156 condition without the grps. one that the left join would work.
Average of ratings: -
In reply to Randy Thornton

Re: Activity report grouped by "groups" in course | Query help

by Nadirshah Ferozepurwalla -
it doesnot work, if I remove the grps course filter then all groups to which the select users are enrolled in across the moodle appear irrespective of the course, even after this the ones not in group do not appear.
Any suggestions/solutions?
Average of ratings: -