Get all rows from a table in a doctrine

Question

I have a table with 100,000+ rows, and I want to select all of it in the doctrine and do some actions with each row, in symfony2 with the doctrine that I am trying to do with this query:

$query = $this->getDefaultEntityManager() ->getRepository('AppBundle:Contractor') ->createQueryBuilder('c') ->getQuery()->iterate(); foreach ($query as $contractor) { // doing something } 

but then I get a memory leak because I think he wrote all the data in memory.

I have more experience in ADOdb, in that library, when I do this:

 $result = $ADOdbObject->Execute('SELECT * FROM contractors'); while ($arrRow = $result->fetchRow()) { // do some action } 

I have no memory leak.

So, how to select all the data from a table and not get a memory leak using a doctrine in symfony2?

EDIT Question

When I try to remove foreach and just repeat, I also get a memory leak:

 $query = $this->getDefaultEntityManager() ->getRepository('AppBundle:Contractor') ->createQueryBuilder('c') ->getQuery()->iterate(); 

Answer 1

The usual approach is to use iterate () .

 $q = $this->getDefaultEntityManager()->createQuery('select u from AppBundle:Contractor c'); $iterableResult = $q->iterate(); foreach ($iterableResult as $row) { // do something } 

However, as stated in the doctrine documentation, this can lead to errors.

The results can be fully buffered by the client / database connection, allocating additional memory that is not visible to the PHP process. For large suites, this can easily kill the process without an apparant reason.

The easiest approach to this is to simply create small queries with offsets and constraints.

 //get the count of the whole query first $qb = $this->getDefaultEntityManager(); $qb->select('COUNT(u)')->from('AppBundle:Contractor', 'c'); $count = $qb->getQuery()->getSingleScalarResult(); //lets say we go in steps of 1000 to have no memory leak $limit = 1000; $offset = 0; //loop every 1000 > create a query > loop the result > repeat while ($offset < $count){ $qb->select('u') ->from('AppBundle:Contractor', 'c') ->setMaxResults($limit) ->setFirstResult($offset); $result = $qb->getQuery()->getResult(); foreach ($result as $contractor) { // do something } $offset += $limit; } 

With these heavy data sets, this is likely to exceed the maximum time. <30 seconds . Therefore, do not forget to manually change set_time_limit in php.ini. If you just want to update all datasets using a well-known template, you should consider writing one big update request instead of looping and editing the result in PHP.

Answer 2

Try using this approach:

 foreach ($query as $contractor) { // doing something $this->getDefaultEntityManager()->detach($contractor); $this->getDefaultEntityManager()->clear($contractor); unset($contractor); // tell to the gc the object is not in use anymore } 

Hope for this help

Answer 3

If you really need to get all the records, I suggest you use database_connection directly. Look at its interface and select a method that will not load all the data into memory (and will not display records in your entity).

You can use something like this (if this code is in the controller):

 $db = $this->get('database_connection'); $query = 'select * from <your_table>'; $sth = $db->prepare($query); $sth->execute(); while($row = $sth->fetch()) { // some stuff } 

This is probably not what you need, because you may want to have objects after processing the entire collection. But perhaps you do not need objects. In any case, think about it.