How to get a real SQL query?

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.

<?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 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:

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 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:

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 config_id = 3, the result will be cached using the 70a9c3853d84b0037586637bc9ab3967 cache key. If you then load item by config_id = 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.

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?

btw for 7.2 you will be able to

https://wiki.php.net/rfc/debugging_pdo_prepared_statement_emulation https://wiki.php.net/rfc/debugging_pdo_prepared_statement_emulation_v2

both rfc were accepted ~month ago

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.

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.