SQL error on a SQLSRV driven Moodle site

[Luis de Vasconcelos]

I'm testing the Adaptable theme $plugin->release = '1.7.1';

It generates invalid SQL for Moodle sites running on MS SQL Server in this section:

https://bitbucket.org/covuni/moodle-theme_adaptable/src/85aac4e07c0c319ea70b4a65c185c5afbeb94eaa/classes/activity.php?at=master&fileviewer=file-view-default#activity.php-809

of \Moodle\theme\adaptable\classes\activity.php

It produces this sql on my test site:

SELECT a.id AS instanceid, st.*
FROM mdl_choice_answers st
JOIN mdl_choice a
ON a.id = st.choiceid
-- Get only the most recent submission.
JOIN (
    SELECT choiceid AS modid, MAX(id) AS maxattempt
    FROM mdl_choice_answers
    WHERE userid = '79448'
    GROUP BY modid
) AS smx
ON smx.modid = st.choiceid
AND smx.maxattempt = st.id
WHERE a.course = '997'
AND st.userid = '79448'
ORDER BY choiceid DESC, st.id DESC

The "GROUP BY modid" is invalid in SQL Server. I got the query to run in SQL Server by replacing it with GROUP BY choiceid

See https://moodle.org/mod/forum/discuss.php?d=378251 for a more detailed description of the error and the environment that I run Moodle on.

[Luis de Vasconcelos]

It seems like I can get it to work by changing the hardcoded "modid" alias to the "$modfield" variable in \classes\activity.php:

@@ +812,7 -812,7 @@
                    JOIN (SELECT $modfield AS modid, MAX(id) AS maxattempt
                    FROM {".$submissiontable."}
                    WHERE userid = ?
+                     GROUP BY $modfield) AS smx
-                     GROUP BY modid) AS smx
                    ON smx.modid = st.$modfield
                    AND smx.maxattempt = st.id

Some initial testing seems to suggest that it works. But, is this the right way to fix it or will it cause other regressions?

[Info 3bits]

Could you test it at least in MySQL and if it is possible in PostgreSQL?

If the tests are succesfull, please submit a PR and we'll integrate it to Adaptable

[Luis de Vasconcelos]

I don't have access to MySQL or PostgreSQL Moodle sites at the moment.

[Luis de Vasconcelos]

MySQL seems to be working.

[Info 3bits]

Thanks, it is working well also in our test server so it will be added in the next release.

[Info 3bits]

fix #951 modify alias by variable

→ <<cset 284739df5bec>>

[Info 3bits]

Issue #977 was marked as a duplicate of this issue.