Symfony 2 - Doctrine 2 - Native Sql - Delete Query

Question

Instead of removing my entities one by one with

$this->em->remove($price);

I would like to execute a native SQL query to delete all my entities.

Here is what I tried :

$sqlQuery = "delete from mytable where mytable.fieldone_id = ".$fieldoneid." and mytable.fieldtwo_id = ".$fieldtwoid.";";

$query = $this->getEntityManager()->createNativeQuery($sqlQuery);

$query->execute();

It returns the following error :

Catchable fatal error: Argument 2 passed to Doctrine\ORM\EntityManager::createNativeQuery() must be an instance of Doctrine\ORM\Query\ResultSetMapping, none given

It wants me to pass a ResultSetMapping, but it is a delete query...

Can anyone please teach me how to do it?

Solution 1

I use a different way of executing native SQL queries that is much easier, in my opinion. Try something like this (I am also using the PDO method of including variables in the query, which is safer):

$sql = "delete from mytable where mytable.fieldone_id = :fieldoneid and mytable.fieldtwo_id = :fieldtwoid";
$params = array('fieldoneid'=>$fieldoneid, 'fieldtwoid'=>$fieldtwoid);

$em = $this->getDoctrine()->getManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute($params);
// if you are doing a select query, fetch the results like this:
// $result = $stmt->fetchAll();

This works great for me, hope it helps

Solution 2

as per Doctrine 2 Native SQL documentation page:

If you want to execute DELETE, UPDATE or INSERT statements the Native SQL API cannot be used and will probably throw errors.

You can user DQL queries instead.

$query = $em->createQuery("DELETE FROM YourNamespace\YourBundle\Entity\YourEntity e WHERE e.fieldone_id = " .$fieldoneid . " AND e.fieldtwo_id = " . $fieldtwoid);
$query->execute();

Solution 3

If you want to use the native way in doctrine, you can use in the entity repository :

public function deleteUserNative(User $user): void
{
    $this->getEntityManager()->getConnection()->delete('user', array('id' => $user->getId()));
}

And just call this in your controller :

$em->getRepository(User::class)->deleteUserNative($user);

Regards,

Comments

[ThatMSG]

You just saved me :) Thx

[Sehael]

The Native SQL API is very different from executing raw SQL (which looks like the goal here). Anyone reading this, do not interpret this answer to mean that you cannot execute raw SQL. DQL is also fine, but sometimes I need to run queries that are too complex for DQL, so raw SQL is the only way.

[Frédéric Marchal]

Passing an array in $params fails with error "Notice: Array to string conversion" (with doctrine 2.5.14). I couldn't use a statement such as "delete from table where id in (:id)" and pass an array to "id". I had to build the full sql statement "delete from table where id in (".implode(",",$id).")".