Symfony2 querybuilder truncate with foreign key and alter table

Question

I have table with several foreign keys. I need to truncate this table and related tables using queryBuilder.

I have found two solutions:

• Define method for each PDO
• using "ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint"

So, I have tried to implement the first solution. According to this example :

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

But if I need to implement this method for each PDO this would be a bit cumbersome.

The second solution looks like more compact, but I have no idea, how to do ALTER TABLE request correct, using queryBuilder.

So, my questions below:

1. How to do ALTER TABLE request in Symfony2 using queryBuilder?
2. What is the most compact way to do truncate table for each PDO?
3. Is it any compact way to truncate not only current table, but each related table?

Thanks a lot for any help!

UPDATED: Also, I have found this, but this looks like out-of-date.

I have found some solution, but I'm not sure it's Symfony way. You can see my solution as answer below.

Answer 1

So, after a lot of googling I have found solution. I hope you can advice me, how to improve this and make more compact.

At first, I'm using onDelete: CASCADE for delete data from related tables;

At second, I have created Doctrine event listener:

public function postRemove(LifecycleEventArgs $args)
{
    $entity = $args->getEntity();

    $em = $args->getEntityManager();
    $qb = $em->getRepository(get_class($entity))->createQueryBuilder('e');

    $id = $qb->select('COALESCE(MAX(e.id)+1, 1)')->getQuery()->getSingleScalarResult();
    $table = $em->getClassMetadata(get_class($entity))->getTableName();

    $sql = sprintf("ALTER TABLE %s AUTO_INCREMENT = %d", $table, $id);
    $em->getConnection()->prepare($sql)->execute();

}

So, for truncate I can use "DELETE FROM table", listener above allow me to reset AUTO_INCREMENT;

But I'm not sure that current solution is Symfony way, please, help me to improve it!