Generate whole SQL statement with binding value to use as a key for cache function - CakePHP 4

Question

Problem Description

I want to cache the query results with the key as a whole SQL statement instead part of the SQL statement like the below example:

// Generate a key based on a simple checksum
// of the query's where clause
$query->cache(function ($q) {
    return md5(serialize($q->clause('where')));
});

Above example taken from this link : https://book.cakephp.org/4/en/orm/query-builder.html#caching-loaded-results

What I have tried

I can get the full SQL without the binding value like this:

$query->sql()

And the binding values like this:

$bindings = $query->getValueBinder()->bindings();

Now I need to figure out how to combine the both. It would be best if there is a built in function in CakePHP which would just give me the SQL with the binding value.

Answer 1

I have found the solution to this. There is a private function in DebugKit named interpolate() which create the full SQL statement with the binding value.

As the function is private, you have to copy it and save it in your source code.

Here's the interpolate function :

    /**
     * Helper function used to replace query placeholders by the real
     * params used to execute the query.
     *
     * @param string $sql The SQL statement
     * @param array $bindings The Query bindings
     * @return string
     */
    private static function interpolate($sql, array $bindings)
    {
        $params = array_map(function ($binding) {
            $p = $binding['value'];

            if ($p === null) {
                return 'NULL';
            }
            if (is_bool($p)) {
                return $p ? '1' : '0';
            }

            if (is_string($p)) {
                $replacements = [
                    '$' => '\\$',
                    '\\' => '\\\\\\\\',
                    "'" => "''",
                ];

                $p = strtr($p, $replacements);

                return "'$p'";
            }

            return $p;
        }, $bindings);

        $keys = [];
        $limit = is_int(key($params)) ? 1 : -1;
        foreach ($params as $key => $param) {
            $keys[] = is_string($key) ? "/$key\b/" : '/[?]/';
        }

        return preg_replace($keys, $params, $sql, $limit);
    }
}

And then call it and pass the SQL and the binding values like this to get the whole SQL statement with the binding values:

$sql = $query->sql();
$bindings = $query->getValueBinder()->bindings();

// to make the example easier, I have saved the interpolate function in controller
$properSqlStatement = $this->interpolate($sql, $bindings);

🎉 Yay !