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).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
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;
$totalRecords = (int) $this->entityManager
    ->createQuery('SELECT COUNT(u.id) FROM App\Entity\User u')
    ->getSingleScalarResult()
;

while (true) {
    $myQuery = $this->entityManager
        ->createQuery('SELECT u FROM App\Entity\User u')
        ->setMaxResults($numberOfRecordsPerPage)
        ->setFirstResult($totalRecordsProcessed)
    ;

    /** @var iterable $iterableResult */
    $iterableResult = $myQuery->toIterable();
    if (empty($iterableResult->current())) {
        break;
    }

    foreach ($iterableResult as $row) {
        $user = $row;

        // do stuff with the data in the row

        // detach from Doctrine, so that it can be Garbage-Collected immediately (*)
        $this->entityManager->detach($row[0]);

        if (($totalRecordsProcessed % $batchSize) === 0) {
            $this->entityManager->flush();
            $this->entityManager->clear();
        }

        $totalRecordsProcessed++;
    }

    if ($totalRecordsProcessed === $totalRecords) {
        break;
    }
}

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

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