search

lsuonline / lsuce-block_quickmail

5 stars 32 forks source link

Non-PostgreSQL compatible SQL #3

Open ericmerrill opened 4 years ago

ericmerrill commented 4 years ago

There are a few places that use ORDER BY clauses when returning a COUNT. Postgres errors, as it doesn't really make sense...

So far we have identified:

  1. notification_repo->get_all_for_course_sql()
  2. queued_repo->get_for_user_sql()
  3. sent_repo->get_for_user_sql()
golenkovm commented 2 years ago

As I can see 3 methods mentioned above are fixed so far. However, another postgres-related issue still exists:

root@cf7f7c4e89bd:/var/www/site# vendor/bin/phpunit --testsuite='block_quickmail_testsuite'
Moodle 3.9.11 (Build: 20211108), 54e892e8fe7407c816f5cae6d11ec91ba8d4c0e3
Php: 7.4.25, pgsql: 9.6.16, OS: Linux 5.4.0-89-generic x86_64
PHPUnit 7.5.20 by Sebastian Bergmann and contributors.

...............................................................  63 / 253 ( 24%)
............................................................... 126 / 253 ( 49%)
..............F...................................FError reading from database (ERROR:  operator does not exist: bigint = text
LINE 9:             AND ra.roleid IN (SELECT value FROM phpu4_config...
                                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
SELECT u.id
            FROM phpu4_user u
            INNER JOIN phpu4_user_enrolments ue ON ue.userid = u.id
            INNER JOIN phpu4_enrol e ON e.id = ue.enrolid
            INNER JOIN phpu4_course c ON c.id = e.courseid
            INNER JOIN phpu4_role_assignments ra ON ra.userid = u.id
            INNER JOIN phpu4_context ctx ON ctx.id = ra.contextid AND ctx.instanceid = c.id
            WHERE u.id NOT IN (SELECT la.userid FROM phpu4_user_lastaccess la WHERE la.courseid = c.id AND la.timeaccess > $1)
            AND ra.roleid IN (SELECT value FROM phpu4_config WHERE name = 'gradebookroles')
            AND c.id = $2
            GROUP BY u.id
[array (
  0 => 1636541826,
  1 => 109000,
)])
golenkovm commented 2 years ago

Hi @rrusso

Could you please have a look at my pull request once you have a chance?

Kind regards, Mikhail