"moosh course-list -e yes" give an error with postgresql #490
Description
Hi,
I try to execute "moosh course-list -e yes" and I got an error
- moosh version: 1.21
- moodle version: 4.2 and 4.4.1
- database: postgresql 15.6
- php version : 8.3.9
- operating system : Red Hat Enterprise Linux release 9.4 (Plow)
Actual behaviour
/usr/local/bin/moosh course-list -e yes
Default exception handler: Erreur de lecture de la base de données Debug: ERREUR: la colonne « modules » n'existe pas
LINE 1: ...c.id=m.course WHERE '1'='1' GROUP BY c.id HAVING modules < ...
^
HINT: Peut-être que vous souhaitiez référencer la colonne « m.module ».
SELECT c.id,c.category,COUNT(c.id) AS modules,c.shortname,c.fullname,c.visible FROM mdl_course c LEFT JOIN mdl_course_modules m ON c.id=m.course WHERE '1'='1' GROUP BY c.id HAVING modules < 2
[array (
)]
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 358 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->read_slave_query_end()
- line 1044 of /lib/dml/pgsql_native_moodle_database.php: call to pgsql_native_moodle_database->query_end()
- line 90 of /usr/local/bin/mooshdir/Moosh/Command/Moodle39/Course/CourseList.php: call to pgsql_native_moodle_database->get_records_sql()
- line 363 of /usr/local/bin/mooshdir/moosh.php: call to Moosh\Command\Moodle39\Course\CourseList->execute()
Expected behaviour
Using column alias in a where clause doesn't work with PostGresql : https://dba.stackexchange.com/questions/225874/using-column-alias-in-a-where-clause-doesnt-work
This is working :
SELECT c.id,c.category,COUNT(c.id) AS modules,c.shortname,c.fullname,c.visible FROM mdl_course c LEFT JOIN mdl_course_modules m ON c.id=m.course WHERE '1'='1' GROUP BY c.id HAVING COUNT(c.id) < 2
Steps to reproduce
Execute /usr/local/bin/moosh course-list -e yes and see the error
Pascal