Custom report SQL error

Hi,

I'm using the VideoTime Pro plugin, with proper license. I'm trying to build a custom report so that teachers can see the time viewed by the students for a specific category.

If I create a table with just column "user full name" and column "Time watched" aggregated with sum, if I add the condition to be inside a specific course category, I get the error below.

The query is clearly broken because there is no join with a table named (or renamed) "c".

I'm using the latest version of Moodle (4.3) and the latest version of VideoTimePro (1.8RC).

If you need any other information, please do not hesitate to contact me.

Unknown column 'c.category' in 'on clause'

            SELECT COUNT(1)
              FROM (SELECT u.firstname AS c0_firstname, u.lastname AS c0_lastname, SUM(vtsd.watched_time) AS c1_watched_time
                      FROM mdl_videotime vt JOIN mdl_course_modules vtcm
                    ON vtcm.instance = vt.id
                JOIN (
                  SELECT module_id,
                         user_id,
                         MIN(id) AS firstid,
                         MAX(id) AS lastid,
                         SUM(time) AS watch_time,
                         MAX(current_watch_time) AS watched_time,
                         MIN(timecreated) AS timestarted,
                         MAX(timemodified) AS timeended,
                         MAX(percent_watch) AS watch_percent,
                         COUNT(DISTINCT uuid)
                             + SUM(CASE WHEN uuid IS NULL THEN 1 ELSE 0 END) AS views
                    FROM mdl_videotimeplugin_pro_session
                   WHERE current_watch_time - start_time > 1
                GROUP BY module_id, user_id) vtsd
                      ON vtsd.module_id = vtcm.id JOIN mdl_user u ON u.id = vtsd.user_id JOIN mdl_course_categories cc
                ON cc.id = c.category
                     WHERE (cc.id = ? OR cc.id IN (
                SELECT id
                  FROM mdl_course_categories
                 WHERE path LIKE ? COLLATE utf8mb4_bin ESCAPE '\\'
            ))
                           GROUP BY c0_firstname, c0_lastname
                   ) rbalias8
[array (
  0 => 3,
  1 => '%/3/%',
)]
Error code: dmlreadexception
* line 494 of /lib/dml/moodle_database.php: dml_read_exception thrown
* line 293 of /lib/dml/moodle_read_slave_trait.php: call to moodle_database->query_end()
* line 1398 of /lib/dml/mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->query_end()
* line 1683 of /lib/dml/moodle_database.php: call to mysqli_native_moodle_database->get_records_sql()
* line 1756 of /lib/dml/moodle_database.php: call to moodle_database->get_record_sql()
* line 1967 of /lib/dml/moodle_database.php: call to moodle_database->get_field_sql()
* line 177 of /reportbuilder/classes/table/base_report_table.php: call to moodle_database->count_records_sql()
* line 374 of /reportbuilder/classes/table/custom_report_table.php: call to core_reportbuilder\table\base_report_table->query_db()
* line 85 of /reportbuilder/classes/output/renderer.php: call to core_reportbuilder\table\custom_report_table->out()
* line 495 of /lib/outputrenderers.php: call to core_reportbuilder\output\renderer->render_custom_report_table()
* line 191 of /reportbuilder/classes/external/custom_report_exporter.php: call to plugin_renderer_base->render()
* line 121 of /lib/classes/external/exporter.php: call to core_reportbuilder\external\custom_report_exporter->get_other_values()
* line 73 of /reportbuilder/classes/output/custom_report.php: call to core\external\exporter->export()
* line 50 of /reportbuilder/classes/output/dynamictabs/editor.php: call to core_reportbuilder\output\custom_report->export_for_template()
* line 87 of /lib/classes/external/dynamic_tabs_get_content.php: call to core_reportbuilder\output\dynamictabs\editor->export_for_template()
* line ? of unknownfile: call to core\external\dynamic_tabs_get_content::execute()
* line 253 of /lib/external/classes/external_api.php: call to call_user_func_array()
* line 83 of /lib/ajax/service.php: call to core_external\external_api::call_external_function()

bdecentgmbh / moodle-mod_videotime

Custom report SQL error #64