Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

How to get a real SQL query?

Hello everybody!

I need to automatically create a unique cache key before the query is executed. This key must be the same no matter what you use to get the data:

1) Using "find" method:

$items = \PathTo\Config::find([
            'config_id = 4',
    ]);

2) Or using query:

$phql = "SELECT * FROM \PathTo\Config WHERE config_id = :config_id:";
$query = $this->getDI()->get('modelsManager')->createQuery($phql);
$query->setBindParams([
    'config_id' => 4
]);

Dumping $query->getSql() returns different results:

In first case it returns:

SELECT core_config_data.config_id, core_config_data.scope, core_config_data.scope_id, core_config_data.path, core_config_data.value FROM core_config_data WHERE core_config_data.config_id = 4

In case with query it returns:

SELECT core_config_data.config_id, core_config_data.scope, core_config_data.scope_id, core_config_data.path, core_config_data.value FROM core_config_data WHERE core_config_data.config_id = :config_id

And in mysql log file I see the following query in both cases:

SELECT core_config_data.config_id, core_config_data.scope, core_config_data.scope_id, core_config_data.path, core_config_data.value FROM core_config_data WHERE core_config_data.config_id = 4

The question: How can I get a real SQL that is send to MYSQL server without ''= :config_id" etc?

edited Apr '17

Short answer: you can't, unless you create your own logic.

But why isn't SELECT core_config_data.config_id, core_config_data.scope, core_config_data.scope_id, core_config_data.path, core_config_data.value FROM core_config_data WHERE core_config_data.config_id = :config_id: good for you? That query won't change, no matter what parameters you pass in.

edited Apr '17
<?php

use Exception;
use Phalcon\Db\Adapter\Pdo;
use Phalcon\Di;
use Phalcon\Events\Event;
use Phalcon\Logger\Adapter\File;

/**
 * Listener for DB
 *
 * Class DbListener
 */
class DbListener
{
    /**
     * @var File
     */
    protected $logger;

    /**
     * DbListener constructor.
     */
    public function __construct()
    {
        $this->logger = new File("sql-log-".date("Y-m-d"'.log'));
    }

    /**
     * Print trace
     *
     * @return mixed
     */
    protected function debug_string_backtrace() {
        $e = new Exception();
        return str_replace(['/vagrant/www/suzuki/'," #"], ['','/n#'], $e->getTraceAsString()); // change your path of script here if you want to have trace
    }

    /**
     * Executed before query being made
     *
     * @param Event $event
     * @param Pdo $connection
     */
    public function beforeQuery(Event $event, Pdo $connection)
    {
        $this->logger->info(
            "Query: ".$connection->getRealSQLStatement().PHP_EOL.
            "Params: ".json_encode($connection->getSqlVariables()).(ENV == 'PROD') ? PHP_EOL. // change condition here when to add trace
            "Trace:".PHP_EOL.$this->debug_string_backtrace() : ''
        );
    }
}

And then in your database service:

$di->set('db', function() {
        // set $adapter
        $eventsManager = new Manager();
        //Listen all the database events
        $eventsManager->attach('db', new DbListener());
        //Assign the eventsManager to the db adapter instance
        $adapter->setEventsManager($eventsManager);

        return $adapter;
})

Oh god i just noticed that im adding trace on prod enviroment :C

Thank you for your reply.

This is not an option.

Let's say we have the method that automatically generates cache keys:

public function generateCachekey($queryString) {

    return md5($queryString);

}

1) If we load item by config_id = 3, the "generateCachekey" method will receive the following as a $queryString:

SELECT coreconfigdata.configid, coreconfigdata.scope, coreconfigdata.scopeid, coreconfigdata.path, coreconfigdata.value FROM coreconfigdata WHERE coreconfigdata.configid = :configid:

The "generateCachekey" will return the following hash: 70a9c3853d84b0037586637bc9ab3967.

2) If we load item by config_id = 6, the "generateCachekey" method will receive the following as a $queryString:

SELECT coreconfigdata.configid, coreconfigdata.scope, coreconfigdata.scopeid, coreconfigdata.path, coreconfigdata.value FROM coreconfigdata WHERE coreconfigdata.configid = :configid:

The "generateCachekey" will return the following hash: 70a9c3853d84b0037586637bc9ab3967.

You can see that both cache key are identical.

This means that if you load item by configid = 3, the result will be cached using the 70a9c3853d84b0037586637bc9ab3967 cache key. If you then load item by configid = 6 - you'll get the cached object with config_id = 3 because they both have the same key.

P. S. I'm talking about the ORM cache.

Short answer: you can't, unless you create your own logic.

But why isn't SELECT core_config_data.config_id, core_config_data.scope, core_config_data.scope_id, core_config_data.path, core_config_data.value FROM core_config_data WHERE core_config_data.config_id = :config_id: good for you? That query won't change, no matter what parameters you pass in.

This is not an option too, because $connection->getRealSQLStatement() doesn't return a real SQL query. In my case it returns:

SELECT core_config_data.config_id, core_config_data.scope, core_config_data.scope_id, core_config_data.path, core_config_data.value FROM core_config_data WHERE core_config_data.config_id = :config_id

Please see my reply to Lajos Bencz above.

What I need is to get the real SQL query with paramteres, not placeholders.



99.6k
Accepted
answer
edited Apr '17

Then there is no such way, pdo itself doesn't have option to return real sql with placeholders bound, it happens in pdo extension itself or even on database engine. Why you can just do json_encode on parameters and do md5 on it or something like this?

You are right, looks like the best I can is to hash $query->parse().

Thank you.

Then there is no such way, pdo itself doesn't have option to return real sql with placeholders bound, it happens in pdo extension itself or even on database engine. Why you can just do json_encode on parameters and do md5 on it or something like this?

Then i guess this will be added to phalcon so getRealSqlStatement will return this if query was executed.

This would be great!

Then i guess this will be added to phalcon so getRealSqlStatement will return this if query was executed.

edited Apr '17

Use RDBMS itself to capture raw queries, during development / optimization phase at least. Not very elegant but PDO has only PDOStatement::debugDumpParams for this purpose.