When I am on the courses tab and click on the Learners actions, and try to order the course list by the "Average Time Spent Per Student" i get this error:
ERROR: column "avg_timespend" does not exist
LINE 58: ORDER BY avg_timespend ASC LIMIT 10
^
SELECT
t.*
FROM (SELECT CONCAT(ra.userid,'_',c.id) as id,
ra.userid AS userid,
c.id as courseid,
ra.timemodified as enrolled,
ul.timeaccess AS timeaccess,
ROUND((CASE WHEN g.rawgrademax > 0 THEN (g.finalgrade/g.rawgrademax)*100 ELSE g.finalgrade END), 0) AS grade,
cc.timecompleted AS timecompleted,
CONCAT(u.firstname, ' ', u.lastname) AS learner,
u.email,
u.firstname AS firstname,
u.lastname AS lastname,
u.alternatename AS alternatename,
u.middlename AS middlename,
u.lastnamephonetic AS lastnamephonetic,
u.firstnamephonetic AS firstnamephonetic,
l.timespend AS timespend,
l.visits AS visits,
cmc.progress AS progress,
'' as actions
FROM (SELECT ra.userid,
cx.instanceid AS courseid,
MIN(ra.timemodified) AS timemodified
FROM m_role_assignments ra
JOIN m_context cx ON cx.id = ra.contextid AND cx.contextlevel = 50
WHERE cx.instanceid = $1 AND ra.roleid = $2
GROUP BY ra.userid, cx.instanceid
) ra
JOIN m_user u ON u.id = ra.userid
JOIN m_course c ON c.id = ra.courseid
LEFT JOIN m_grade_items gi ON gi.itemtype = 'course' AND gi.courseid = c.id
LEFT JOIN m_user_lastaccess ul ON ul.courseid = c.id AND ul.userid = u.id
LEFT JOIN m_course_completions cc ON cc.course = c.id AND cc.userid = ra.userid
LEFT JOIN m_grade_grades g ON g.userid = u.id AND g.itemid = gi.id AND g.finalgrade IS NOT NULL
LEFT JOIN (SELECT ue.userid, MIN(ue.status) AS status, e.courseid
FROM m_user_enrolments ue
JOIN m_enrol e ON ue.enrolid = e.id AND e.courseid = $3
GROUP BY ue.userid, e.courseid
) enr ON enr.userid = u.id
LEFT JOIN (SELECT cmc.userid, COUNT(DISTINCT cmc.id) as progress
FROM m_course_modules_completion cmc
JOIN m_course_modules cm ON cm.visible = 1 AND cmc.coursemoduleid = cm.id AND
cm.completion > 0 AND cm.course = $4
WHERE cm.id > 0 AND cmc.completionstate IN(1,2)
GROUP BY cmc.userid
) cmc ON cmc.userid = u.id
LEFT JOIN (SELECT t.userid,t.courseid, sum(t.timespend) as timespend, sum(t.visits) as visits
FROM m_local_intelliboard_tracking t
GROUP BY t.courseid, t.userid
) l ON l.courseid = c.id AND l.userid = u.id
WHERE ra.courseid > 0 AND u.deleted = 0 AND u.suspended = 0 AND u.username <> 'guest' AND enr.status = 0 AND c.id IN (23306,26163,26792,28563,30632,30700)) t
WHERE t.userid > 0
ORDER BY avg_timespend ASC LIMIT 10
-- line 2069 of /lib/tablelib.php: call to pgsql_native_moodle_database->get_records_sql()
Error code: dmlreadexception
* line 486 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 329 of /lib/dml/pgsql_native_moodle_database.php: call to moodle_database->query_end()
* line 977 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
* line 2069 of /lib/tablelib.php: call to pgsql_native_moodle_database->get_records_sql()
* line 2091 of /lib/tablelib.php: call to table_sql->query_db()
* line 278 of /local/intelliboard/instructor/courses.php: call to table_sql->out()
I guess it makes no sense to order a list by the average (it will be the same for all of them), so instead of fixing the SQL itself it may be a better idea to disable the ordering option to that column.
It happens to us on Moodle 3.11.3 on PostreSQL with local_intelliboard 7.0.0 (2021090604)
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent