Problem with SQL - Moodle 3.8

Problem with SQL - Moodle 3.8

by Dave Emsley -
Number of replies: 3

Hi All,

Banging my head against a wall here with an SQL query which works sometimes.

SELECT ue.id as id, 
u.id AS UserID,
c.id AS CourseID,
cat.id AS CatID,
c.fullname, u.firstname, u.lastname, u.email, u.department, c.visible as coursevis,
cp.timeenrolled, cp.timestarted, cp.timecompleted,
cat.name AS catname,
cat.visible AS catvis,
e.roleid AS role,
uid.fieldid, uid.data
FROM {user_enrolments} AS ue
JOIN {user} AS u ON ue.userid = u.id
JOIN {user_info_data} AS uid ON uid.userid = u.id
JOIN {enrol} AS e ON e.id = ue.enrolid JOIN {course} AS c ON e.courseid = c.id
JOIN {course_categories} AS cat ON c.category = cat.id
LEFT JOIN {course_completions} AS cp ON (c.id = cp.course AND u.id=cp.userid)
WHERE e.roleid > 3 AND c.visible = 1 AND cat.visible = 1 AND uid.fieldid = :wibble

Params is...Array ( [wibble] => 1 )

This fails in Moodle with no error messages (full debugging and SQL error reporting are on).

However it works directly in PHPMyAdmin.

It also works if I add  "AND c.id = 4" to the end of the SQL statement.


I'm stumped here - any advice and thoughts appreciated.

Cheers

Dave




Average of ratings: -
In reply to Dave Emsley

Re: Problem with SQL - Moodle 3.8

by Dave Emsley -
Sorry Moodle 3.11.6+ Not 3.8 a per title.

It is working on the testing server but not on production and the server systems are *almost* identical.

Testing is running MariaDB 10.6.7 and production is running 10.5.15 but as the SQL is identical I cannot see why that should make a difference as the raw SQL works on PHPMyAdmin

Thoughts please?

Dave
Average of ratings: -
In reply to Dave Emsley

Re: Problem with SQL - Moodle 3.8

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
You're going to have to define what "fails in Moodle" means.

Do you mean, the application stops instantly, without an error, when you run the query?

Or do you mean the query runs OK, but doesn't produce any results?

The JOIN with {user_info_data} looks very dodgy - users can have multiple {user_info_data} entries, so I'd be wanting to add the 'uid.fieldid = :wibble' to the JOIN clause.

My usual way of investigating such issues is to use xdebug, set a breakpoint on the $DB->get_records_sql() call (or whichever of those functions you're using), then step down into the core code, until the point where it has generated the final SQL that will be run against the DB, then copy + paste that directly into PHPMyAdmin (or even better, Adminer, as it is vastly better than PHPMyAdmin) - then you know the query you are testing is exactly what is being run by Moodle.
Average of ratings: Useful (1)