TRUNCATE TABLE with Symfony and Doctrine

November 20, 2009 at 8:59 pm Christopher Shennan 3 comments

I am often importing data into a symfony project from an existing site or a CSV file and re-writing this data into the fixtures.yml is usually too complicated or time consuming so I end up writing bespoke import actions.

While I am writing these actions I often find that I have to delete the contents of the MySQL tables as you can never write the routine complete and accurate in one go so as you build it up in smaller steps you find that you have to get rid of the previously old data.

This presented me with 2 problems:-

Using DELETE FROM [TABLENAME] does not reset the auto-incrementing fields so if you have anything that is based on the id during the rest of the import then you’re going to have issues.
There is a lot of clicking in phpMyAdmin to empty the tables, especially if you forget about the foreign key relationships.

I found that MySQL had a TRUNCATE TABLE but this is not directly available via Doctrine as the TRUNCATE TABLE is function of the type of database you are using and Doctrine cannot assume you are always using MySQL so therefore it cannot provide a generic method to call it.

Having said that it was not too difficult to run the TRUNCATE TABLE from within my Symfony project. All I had to do was add the following lines of code to an action within the actions.class.php for my import module and now it empties each table I have defined before importing the data and it also resets the auto-incrememt ids all my tables start from id = 1 each time I run the import.

$doctrine = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
$doctrine->query('TRUNCATE TABLE order_items');
$doctrine->query('TRUNCATE TABLE orders');
unset($doctrine);