symfonydoctrine-ormdoctrinedqldoctrine-query

Doctrine query: delete with limit

I am trying to delete only x objects with a delete query from Doctrine. And since there is no LIMIT in doctrine, we should use $query->setMaxResults($limit) instead. I am using Symfony2.

However it does not work with the following query (with or without $query->setMaxResults($limit), it delete everything instead of deleting the $limit first entities).

$limit = 10;
$query = $entityManager->createQuery(
        'DELETE FROM MyProject\Bundle\MyBundle\Entity\MyEntity myEntity
         WHERE myEntity.cost = 50'
    )
$query->setMaxResults($limit);
$query->execute();
Solution

  • One solution that works is to use native SQL with Doctrine like this (instead of DQL).

    $limit = 10;
$sql    = 'DELETE FROM my_entity
           WHERE cost = 50
           LIMIT ' . $limit;
$stmt = $entityManager->getConnection()->prepare($sql);
$stmt->execute();