Moodle — Debugging SQL

24th May 2022 Guy Thomas

Moodle — Debugging SQL

Most Moodle developers will have hit this problem: You write what you think is a *perfect* SQL query but it doesn’t work. Then you think — ah, what I need to do now is to run the SQL directly BUT now I have to convert all the tables from {tablename} to mdl_tablename. Also, I have to take all the query parameters and manually interpolate them into the SQL. What a pain!

Well, this is what I do — I have this function in my local config.php file:

if (!function_exists(‘dd_sql’)) {

/**
* Var dump sql with params interpolated.
* NOTE: DO NOT INCLUDE THIS IN PRODUCTION.
* @author: Guy Thomas Citricity Ltd 2018
*
* @param string $sql
* @param array|null $params
* @param int $mode
*/
function dd_sql($sql, $params = null, $mode = SQL_PARAMS_QM) {
global $CFG;
$sql = str_replace(‘{‘, $CFG->prefix, $sql);
$sql = str_replace(‘}’, ”, $sql);

if ($mode === SQL_PARAMS_QM) {
foreach ($params as $param) {
$sql = substr_replace($sql, “‘$param'”, strpos($sql, ‘?’), 1);
}
} else {
$pkeys = array_keys($params);

// Note the reversal is important – it means if you have :cat and :cat2, :cat2 will get replaced first.
$pkeys = array_reverse($pkeys, true);
foreach ($pkeys as $key) {
$val = $params[$key];
$sql = str_replace(‘:’ . $key, “‘$val'”, $sql);
}
}

mtrace($sql);
exit;
}
}

And now I can simply add the following to my code:

dd_sql($sql, $params)

Boom! I now have a fully executable query ready to paste into the CLI.

If the query uses an array of unnamed query params you use:

dd_sql($sql, $params)

Or if the query uses named params you use:

dd_sql($sql, $params, SQL_PARAMS_NAMED);

Now I can debug my SQL outside of my moodle code.

Source: Stories by Guy Thomas on Medium