How to perform a huge loop in PHP + Doctrine?

Question

I'm trying to develop a simple "Ranking" script in PHP using Symfony2 (MongoDB+Doctrine).

    $users = $dm->createQueryBuilder('Account')
                ->sort('points', 'DESC')
                ->getQuery()
                ->execute();

    foreach($users as $user){
            // Do nothing
    }

I get the following problem:

PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 25 bytes)

My database have 80.000 users, so $users have all of them.

Well, I know I can modify php config to allow more memory. And I'm going to do it. But I'm looking for other type of solution (apart of adding extra memory to PHP config).

The think is I've tried to develop it with a "pagination" but it doesn't work either (I get the error at user 272, 3rd iteration) :

    $limit = 100;
    $skip = 0;

    $total = $dm->createQueryBuilder('Account')->count()->getQuery()->execute();
    $max = $total/$limit;

    while($skip<=$max){

        $users = $dm->createQueryBuilder('Account')
            ->sort('points', 'DESC')
            ->limit($limit)
            ->skip($skip*$limit) // 0x100, 1x100, 2x100, 3x100
            ->getQuery()
            ->execute();

        foreach($users as $user){
               // Do nothing
        }

        $skip++;

    }

Do you have some idea how could I do it?

PS: I think I've done what is suggested in this post: How to handle huge select query with symfony and doctrine?

Answer 1

See the documentation for the iterate() method. It's designed for exactly this purpose.

You can use the iterate() method just to iterate over a large result and no UPDATE or DELETE intention. The IterableResult instance returned from $query->iterate() implements the Iterator interface so you can process a large result without memory problems...

Don't forget to detach the result as you use it.

Answer 2

Doctrine keeps all the entities in memory. So for batch updates you need to clear the cache.

$dm->flush();
$dm->clear(); // Detaches all objects from Doctrine!