How to truncate a table using Doctrine?

Question

I want to empty a table in my MySQL database. How can I do that with Doctrine?

Answer 1

Truncating a table with Doctrine is as "simple" as:

$connection = $entityManager->getConnection();
$platform   = $connection->getDatabasePlatform();

$connection->executeUpdate($platform->getTruncateTableSQL('my_table', true /* whether to cascade */));

But you have to know that MySQL will not be able to truncate any table once it has a foreign key constraint.

Answer 2

You can truncate data in MySQL via Doctrine so that it ignores foreign key constraints...

$connection->executeQuery('SET FOREIGN_KEY_CHECKS = 0;');
$truncateSql = $platform->getTruncateTableSQL('table_name');
$connection->executeUpdate($truncateSql);
$connection->executeQuery('SET FOREIGN_KEY_CHECKS = 1;');

Answer 3

I generalized the answer before to a nice function which I have used in my project, feel free to share.

/** 
 * @param array $tableNames Name of the tables which will be truncated.
 * @param bool $cascade 
 * @return void
 */
 public function truncateTables($tableNames = array(), $cascade = false) {
    $connection = $this->em->getConnection();
    $platform = $connection->getDatabasePlatform();
    $connection->executeQuery('SET FOREIGN_KEY_CHECKS = 0;');
    foreach ($tableNames as $name) {
        $connection->executeUpdate($platform->getTruncateTableSQL($name,$cascade));
    }
    $connection->executeQuery('SET FOREIGN_KEY_CHECKS = 1;');
 }

Answer 4

If you have a problem with foreign key I working with :

$connection = $this->em->getConnection();
$connection->beginTransaction();

$connection->query('DELETE FROM reception_detail');
$connection->query('ALTER TABLE reception_detail AUTO_INCREMENT = 1');

Answer 5

If you want to remove entities including associated entities that are eventually connected by foreign keys you could use a simply DQL batch query instead of truncating:

$q = $em->createQuery('delete from AppBundle\Entity\Customer');
$numDeleted = $q->execute();

http://doctrine-orm.readthedocs.org/en/latest/reference/batch-processing.html#dql-delete

This will only work with associations if you correctly configured cascade operations and orphanRemoval e.g.:

class Customer
{
    /**
     * @ORM\OneToOne(targetEntity="Address", cascade={"all"}, orphanRemoval=true)
     */
    public $address;
}

This is not a direct answer regarding the MySQL TRUNCATE command but since it is realted to Doctrine this approach could solve your issue.

Answer 6

Short variant (most useful in migrations) !

Doctrine_Manager::getInstance()->getConnection('doctrine')->getDbh()->exec("TRUNCATE name");