Doctrine - how to get the SQL INSERT query, in the postSave() event?

Question

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.

Answer 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.

Answer 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.