Doctrine select to iterable with batch

I was looking for examples on how to do processing on possibly a lot of result (millions) from a doctrine select operation. Then I stumbled upon a stackoverflow question, a blog page, and doctrine docs. While it helped a lot, the stackoverflow answer and the blog page is using functions from older doctrine version. And I decide to post my own ‘updated’ version.

I am using pinned version of each library for my projects. So here is my current composer.json version at the time of this writing: (symfony = v5.4.*, doctrine/orm = 2.14.1, doctrine/doctrine-bundle = 2.9.0).

printf("Starting with memory usage: %d MB\n", \memory_get_usage(true) / 1024 / 1024);

$batchSize = 1000; // flush for every batch-size
$numberOfRecordsPerPage = 5000; // number of records for each SQL query
$totalRecordsProcessed = 0;

while (true) {
    $myQuery = $this->entityManager
        // ORDER BY matters: setFirstResult() paging is only stable with a
        // deterministic sort, otherwise rows can be skipped or repeated.
        ->createQuery('SELECT u FROM App\Entity\User u ORDER BY u.id ASC')
        ->setMaxResults($numberOfRecordsPerPage)
        ->setFirstResult($totalRecordsProcessed)
    ;

    $recordsInThisPage = 0;

    // toIterable() yields the entity itself, not a [0 => $entity] array
    // like the old iterate() did.
    foreach ($myQuery->toIterable() as $user) {

        // do stuff with the data in $user

        $recordsInThisPage++;
        $totalRecordsProcessed++;

        // clear() detaches everything so the identity map stays flat.
        // Do not detach $user before this, or your changes never get flushed.
        if (($totalRecordsProcessed % $batchSize) === 0) {
            $this->entityManager->flush();
            $this->entityManager->clear();
        }
    }

    // short page means we reached the end
    if ($recordsInThisPage < $numberOfRecordsPerPage) {
        break;
    }
}

$this->entityManager->flush();
$this->entityManager->clear();

printf("Ending with memory usage: %d MB\n", \memory_get_usage(true) / 1024 / 1024);

Two things that trip people up when copying the older answers across:

toIterable() gives you the entity directly. The old iterate() wrapped it in an array, which is why so many snippets say $row[0]. That indexing will just break now.

Do not detach() each row as you go. Those answers were written for read-only passes. If you are modifying entities, detaching one before the flush() means the change is silently dropped. The clear() after each flush is enough to keep memory flat.