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

score 3 · Accepted Answer · edited Oct 11 '22 at 10:23

3

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

edited Oct 11 '22 at 10:23

Armin

15,582
10
47
64

answered Aug 15 '14 at 07:57

msusplugas

713
3
8
16

This works well but worth noting that if you go native you will loose any of the delete cascading you'd get through DQL. – Bananaapple Jun 07 '23 at 10:28

score 1 · Answer 2 · answered Sep 22 '22 at 09:53

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

score 0 · Answer 3 · answered May 06 '21 at 14:42

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.

Doctrine query: delete with limit

3 Answers3