Doctrine query: delete with limit

Question

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();

Overcautious

Answer 1

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();

Overcautious

This works well but worth noting that if you go native you will loose any of the delete cascading you'd get through DQL. – Batchelder

Answer 2

Use a sub query so you can use setMaxResults

$qb = $this->em->getRepository(MyClass::class)->createQueryBuilder('x');
$subQb = $this->em->getRepository(MyClass::class)->createQueryBuilder('x_sub');

// We can not use "setMaxResults" on delete query so we need a sub query
$subQb
    ->select('x_sub.id')
    // ... your where clauses
    ->setMaxResults(500)
;

$qb
    ->delete()
    ->andWhere($qb->expr()->in('x.id', ':ids'))
    ->setParameter('ids', $subQb->getQuery()->getResult())
;

Beatify

Answer 3

setMaxResults works only in some cases. Doctrine seems to ignore it if it's not managed.

check the Doctrine doc : https://www.doctrine-project.org/projects/doctrine1/en/latest/manual/dql-doctrine-query-language.html#driver-portability

If it does not work, try in native SQL, like the other solution posted.

Wrecker