How to get SQL INSERT query in postSave () event?

Question

I would like to receive the exact SQL INSERT query that Doctrine generates when the save () method is called.

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

For example:

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

I want to receive / register 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'); 

A prerequisite for this is that I want to mass import data by analyzing the txt file.

Answer 1

I don't think there is an easy way to do this, since the save () behavior depends on several different things (if you are inserting / updating).

If you created a doctrine query object, you can call the getSqlQuery () method as follows:

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

but save () is a method, not an object, so this will not work. I think you will have to crack some kind of code to determine if you are inserting or updating and building a login request on the fly, and then use save ().

I know that this sentence is not ideal, because it does not record “exactly” what save () does, but for the purposes you stated, it should still work.

Answer 2

Take a look here: http://www.phpandstuff.com/articles/codeigniter-doctrine-scratch-day-8-hooks-profiling-dql and go to Profiling with Doctrine and Creating a Profiler Hook . Although it is used for use with the CodeIgniter Framework, it can be easily adopted in your own environment, as the code has no structure dependencies.

Basically you want to configure Profiler Connection and let it write all requests to a file. I suggest adding all the requests to the file in order to have a better “logarithmic” feeling. Do not confuse the many framework discussions within the articles. The examples work very well (with a little understanding and copy and paste) in other scenarios.

Answer 3

you can use the profiler from the sfDoctrineDatabase class. Use getQueryExecutionEvents to capture 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 logger // sfContext::getInstance()->getLogger()->debug(sprintf('Query Run !! %s ', $query)); } } } } 

Do not forget to join the request with the parameters (why will it replace? Values)

: D

Answer 4

Why don't you try how symfony developers do it? Check out their WebDebug toolbar for Doctrine here . The WebDebug toolbar displays the entire request that you make on the page.

There is a hook on DoctrineEvent , and I think you can change the code to do what you want. Check the getDoctrineEvents and getSqlLogs .

Hope this helps. If you need an additional explanation, write in the comments, I will try to explain everything.

Answer 5

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