Moodle with Oracle (show results) · Error: ORA-00979

Hello,

Infrastructure:
- Moodle 3.4.1
- php-7.1
- oracle 11g
- H5P (mod_hvp_moodle34_2018012200.zip)

We found an error at the time of performing the SQL query to show the results: Error: ORA-00979 (http://www.dba-oracle.com/t_ora_00979_not_a_group_by_expression.htm)

And we have modified the following lines: /hvp/classes/results.php

> IN LINE 194:
        // parser $fields
        $fieldsgroup = preg_replace("/AS [a-zA-Z_]*\, /", ", " ,$fields);

> IN LINE 199
        //$groupby = ' GROUP BY g.id, u.id, i.iteminstance, x.id';
        $groupby = ' GROUP BY g.id, '. $fieldsgroup .' g.rawgrade, g.rawgrademax, g.timemodified, x.id, i.iteminstance';

> IN LINE 212
        x.id as xapiid,
        i.iteminstance

The modifications would look like this:
--
        // parser $fields
        $fieldsgroup = preg_replace("/AS [a-zA-Z_]*\, /", ", " ,$fields);

        // Join on xAPI results.
        $join .= ' LEFT JOIN {hvp_xapi_results} x ON i.iteminstance = x.content_id AND g.userid = x.user_id';
        //$groupby = ' GROUP BY g.id, u.id, i.iteminstance, x.id';
        $groupby = ' GROUP BY g.id, '. $fieldsgroup .' g.rawgrade, g.rawgrademax, g.timemodified, x.id, i.iteminstance';

        // Get from statement.
        $from = $this->get_from_sql();

        // Execute query and get results.
        return $this->get_sql_results("
                SELECT g.id,
                       {$fields}
                       g.rawgrade,
                       g.rawgrademax,
                       g.timemodified,
                       x.id as xapiid,
                       i.iteminstance
                  {$from}
                  {$join}
                  {$where}
                  {$groupby}
                  {$orderby}
                ", $args,
                $this->offset,
                $this->limit);
    }
--

We would like the H5P team to review the code, confirm that the modification is correct (if there is another more optimal query, we would appreciate being told) and that it will be applied for future versions.

Thank you so much for everything.

Sincerely,

Iván Sibillà

P.S. Is there any way to access the results without having to go through the "Gradebook"?