Doctrine - how to get the SQL INSERT query, in the

问题:

I would like to get the exact SQL INSERT query that Doctrine generates when an object's save() method is called.

Preferably, I would like to get it in the postSave() event of the model and log it in a txt file.

For instance:

<?php 
$user = new User(); // A Doctrine Model with timestampable behavior enabled
$user->first_name = 'Manny';
$user->last_name = 'Calavera';
$user->save();
?>

I want to get/log the following SQL query:

INSERT INTO user (first_name, last_name, created_at, updated_at) VALUES ('Manny', 'Calavera', '2010-08-03 12:00:00', '2010-08-03 12:00:00');

The background for needing this, is that I wish to mass-import later the data by parsing the txt file.

回答1:

I don't think there is any easy way to do this since the behaviour of save() varies depending on a few different things (if you're inserting/updating).

If you had created a doctrine query object, then you can call the getSqlQuery() method like this:

$q = Doctrine_Query::create()
->select('u.id')
->from('User u');

echo $q->getSqlQuery();

but the save() is a method, not an object so this won't work. I think you'll have to hack in some code to detect whether you're inserting or updating and build a query to log on the fly and then use save().

I know this suggestion is not ideal because it is not logging 'exactly' what save() is doing but for the purposes you stated it should still work just as well.

回答2:

Take a look here: http://www.phpandstuff.com/articles/codeigniter-doctrine-scratch-day-8-hooks-profiling-dql and go to the section headed Profiling with Doctrine and Creating a Profiler Hook. Altough this is for the use with the CodeIgniter framework, it can be easy adopted to your own environment since the code has no dependencies to the framework.

You basically want to set up a Connection Profiler and let it write all queries to a file. I suggest appending all queries to the file to have a better "log"-like feeling. Don't get confused by many framework talk inside the articles. The examples work very well (with a little understanding and copy&pasting) in other scenarios.

回答3:

you can use the profiler from the sfDoctrineDatabase class. Use the getQueryExecutionEvents to grab all queries.

$databaseManager = sfContext::getInstance()->getDatabaseManager();
if ($databaseManager) {
    foreach ($databaseManager->getNames() as $name) {
        $database = $databaseManager->getDatabase($name);
        if ($database instanceof sfDoctrineDatabase && $profiler = $database->getProfiler()) {
            foreach ($profiler->getQueryExecutionEvents() as $event) {
                $conn = $event->getInvoker() instanceof Doctrine_Connection ? $event->getInvoker() : $event->getInvoker()->getConnection();
                $params = sfDoctrineConnectionProfiler::fixParams($event->getParams());
                $query = $event->getQuery() ;

                foreach ($params as $param) {
                    $param = htmlspecialchars($param, ENT_QUOTES, sfConfig::get('sf_charset'));
                    $query = join(var_export(is_scalar($param) ? $param : (string) $param, true), explode('?', $query, 2));
                }
                // log the $query here, or use the symfony's logger
                // sfContext::getInstance()->getLogger()->debug(sprintf('Query Run !! %s ', $query));
            }
        }
    }
}

dont forget to join the query with the parameters (so it will replace ? with the values )

:D

回答4:

Why don't you try to see how the symfony developers do it? Check their WebDebug Toolbar for Doctrine here. The WebDebug Toolbar outputs all query that you do on a page.

There's a hook on DoctrineEvent, and I think you can modify the code to do what you want. Check the getDoctrineEvents and getSqlLogs method.

Hope this helps. If you need further explanation, please write it in comment, I'll try my best to explain.

回答5:

You can convert you DQL query to SQL by this function: http://tokarchuk.ru/2010/12/dql-query-to-raw-sql/