Is there a way to add a LIMIT to an UPDATE query in Doctrine ORM?

Question

I am using Doctrine 2.5.x and I am having problems with getting the LIMIT clause to work for UPDATE queries. It always updates all matched records (i.e. it seems to ignore the LIMIT clause).

setMaxResults() seems to have no effect when used together with UPDATE queries.

As a quick workaround I am using a native MySQL query but that cannot be the best solution.

I tried these examples but none are working:

Doctrine update query with LIMIT

https://recalll.co/app/?q=doctrine2%20-%20Doctrine%20update%20query%20with%20LIMIT

QueryBuilder with setMaxResults() (does not work):

$qb = $em->createQueryBuilder();

$query = $qb->update('\Task\Entity', 't')
    ->set('t.ClaimedBy', 1)
    ->where('t.Claimed IS NULL')
    ->getQuery();
$query->setMaxResults(20);

$this->log($query->getSQL());

Hope someone can help in finding a better solution than a native query. It takes away the whole benefit of the ORM.

Is it even possible to use a LIMIT clause in an UPDATE statement?

Answer 1

In short, no, because the SQL specification does not support UPDATE ... LIMIT ..., so none of the ORM trying to achieve portability should allow you to do it.

---

Please also have a look at MySQL Reference Manual itself stating that UPDATE ... LIMIT ... is not a standard SQL construction:

MySQL Server supports some extensions that you probably will not find in other SQL DBMSs. Be warned that if you use them, your code will not be portable to other SQL servers. In some cases, you can write code that includes MySQL extensions, but is still portable, by using comments of the following form:

• SQL statement syntax
  • The ORDER BY and LIMIT clauses of the UPDATE and DELETE statements.

So by essence because what you are trying to achieve is not standard SQL the ORM will not have a portable way to implement it and will probably not implement it at all.

---

Sorry, but what you are trying to achieve is not possible through DQL, because:

Ocramius commented on Sep 2, 2014
DQL doesn't allow limit on UPDATE queries, as it is not portable.

As suggested in this issue of DoctrineBundle repository by its owner, Marco Pivetta (he also happen to be the owner of the ORM repository).

Further information, although it might needs a good link to the right ISO specification documentation that is sadly not freely available:

The ISO standard of UPDATE instruction do not allow LIMIT in an UPDATE, where SELECT is, of course, an instruction that does allow it.

As you were raising it by yourself, the purpose of an ORM is to not write pure SQL in order to have it cross DBMS compatible. If there is no possibility to make it that way, then it makes sense that the ORM does not implement it.

Also note that on other SQL variant than MYSQL, the limit is actually part of the SELECT clause:

select * from demo limit 10

Would translate in a SQL Server to

select top 10 from demo

Or in Orcale to

select * from demo WHERE rownum = 1

Also see: https://stackoverflow.com/a/1063937/2123530

Answer 2

As b.enoit.be already stated in his answer, this is not possible in Doctrine because using LIMIT's in an UPDATE statement is not portable (only valid in MySQL).

Hope someone can help in finding a better solution than a native query. It takes away the whole benefit of the ORM.

I would argue that you are mixing business rules with persistence (and the ORM does not play well with that, luckily).

Let me explain: Updating an entity's state is not necessarily a business rule. Updating max. 20 entities is (where does that 20 come from?).

In order to fix this, you should properly separate your business rules and persistence by separating it into a service.

class TaskService
{

    private $taskRepository;

    public function __construct(TaskRepository $taskRepository)
    {
        $this->taskRepository = $taskRepository;
    }

    public function updateClaimedBy()
    {
        $criteria = ['Claimed' => null];
        $orderBy = null;

        // Only update the first 20 because XYZ
        $limit = 20;

        $tasks = $taskRepository->findBy($criteria, $orderBy, $limit);

        foreach($tasks as $task) {
            $task->setClaimedBy(1)
        }
    }

}