Logging of any PHQL statement

[wurst-hans]

As seen in documentation one can log SQL statements by attaching listener to db:beforeQuery event.

Sadly, I cannot find an equivalent for PHQL statements. Interactions with models are logged correctly, but when executing something like

$phql = "DELETE FROM " . Sessions::class . " WHERE timeout<NOW()";
$this->di->get('modelsManager')->executeQuery($phql);

nothing is logged. Which possibilities do I have to get them logged?

[borisdelev]

Well... i will share my db connection service and you can try to use parts u need. All queries are logged.

/**
 * Database connection is created based in the parameters defined in the configuration file
 */
$di->set('db', function () use ($config) {
    $connection = new \Phalcon\Db\Adapter\Pdo\Mysql([
        'host'      => \Config::system()->database->host,
        'username'  => \Config::system()->database->username,
        'password'  => \Config::system()->database->password,
        'dbname'    => \Config::system()->database->name,
        'charset'   => \Config::system()->database->charset,
        'options'   => [
            Pdo::MYSQL_ATTR_INIT_COMMAND => "SET NAMES " . \Config::system()->database->charset,
            Pdo::MYSQL_ATTR_LOCAL_INFILE => true
        ]
    ]);

    // Log mysql queries with parameters (only in development mode)
    // File mysql.log will be cleared on every request
    
    if ($config->development) {
        if (file_exists(BASE_PATH . 'cache/tmp/mysql.log')) {
            unlink(BASE_PATH . 'cache/tmp/mysql.log');
        }
        
        $adapter = new \Phalcon\Logger\Adapter\Stream(BASE_PATH . 'cache/tmp/mysql.log');
        $logger = new \Phalcon\Logger('messages', ['main' => $adapter]);
        
        $eventsManager = new \Phalcon\Events\Manager();
        $eventsManager->attach('db:beforeQuery', function (\Phalcon\Events\Event $event, $connection) use ($logger) {
            $sql = PHP_EOL . $connection->getRealSQLStatement() . PHP_EOL . json_encode($connection->getSqlVariables()) . PHP_EOL;

            $logger->log(\Phalcon\Logger::INFO, $sql);
        });
        $connection->setEventsManager($eventsManager);
    }

    return $connection;
});

Good luck!

[wurst-hans]

My mistake. I have setup connection and logging same like your example. The problem was, that my statement from above is executed as an SELECT * FROM <table> WHERE timeout<NOW() followed by DELETE FROM <table> WHERE id=<id>. I was confused because my raw statement was missing. Now I know, that Phalcon splits it up into SELECT and DELETE