The difference between calling flush () inside or outside the foreach loop, which one to use?

Question

I have had such a doubt for a while, but now is the time to ask about it. See the code below and the presence of huge elements in $someVar , for example 200 elements:

 // First approach foreach($someVar as $item) { $item = $em->getRepository('someEntity')->find($item['value']); $em->remove($item); $em->flush(); } // Second approach foreach($someVar as $item) { $item = $em->getRepository('someEntity')->find($item['value']); $em->remove($item); } $em->flush(); 

• Will both calls do the same? What does deleting records from the database mean?
• At the performance level, which is best used? (The doctrine sometimes behaves like a memory killer)
• If both approaches are good, can I use the same UPDATE query too?
• If any of the requests fails for some reason, how will I catch which? And maybe the error given by Doctrine

Real Case Testing

Having the good information provided in the answer, some doubts remain in my mind. Take a look at this piece of code:

 foreach ($request->request->get( 'items' ) as $item) { $items = $em->getRepository( 'AppBundle:FacturaProductoSolicitud' )->find( $item['value'] ); try { $em->remove( $items ); $em->flush(); array_push($itemsRemoved, $item['value']); $response['itemsRemoved'] = $itemsRemoved; $response['success'] = true; } catch ( \Exception $e ) { dump( $e->getMessage() ); array_push($itemsNonRemoved, $item['value']); $response['itemsNonRemoved'] = $itemsNonRemoved; $response['success'] = false; } } 

I use the try {} catch() {} clause here because I need to know which $item['value'] been deleted or not, to add it to the correct array (see $itemsRemoved and $itemsNonRemoved ) also flush() is executed for each loop, the wrong practice that I know, but by pulling flush from the foreach loop and doing a try-catch inside, is there a way to get which $item['value'] been deleted or not? How?

Answer 1

In fact, Running flush () after each deletion is antipattern. Ideally, you should run it once at the end of all your queries.

For the most part, Doctrine 2 already takes care of proper transaction demarcation for you: all write operations (INSERT / UPDATE / DELETE) are queued until EntityManager # flush () is called, which wraps all these changes in one transaction.

However, you can wrap your queries in a transaction if you want more consistency. In fact, it encourages Doctrine, as you can read in best practices .

Will both calls do the same? What does deleting records from the database mean?

Yes, although the call to remove does NOT essentially result in the immediate release of SQL DELETE in the database. The entity will be deleted the next time EntityManager # flush () is called, which includes this object. This means that objects scheduled for deletion can still be queried and appear in the query and collection results.

Thus, purging inside the loop means a lot of SQL queries and calls to your database, and entities will be immediately deleted.

Dumping outside the loop means one efficient transaction (one access to your database) performed by Doctrine, but entities will not be deleted in fact until a flash is called. Objects will only be marked as deleted.

At the performance level, which is best used? (The doctrine sometimes behaves like a memory killer)

Go beyond the cycle without a doubt. This is the best practice. There may be times when you really need to flash every time you save / delete / update an entity, but very little.

If both approaches are good, can I use the same UPDATE query?

The same applies to updating / saving. Try to avoid stripping the inner loop at all costs.

To complete the work, review the documentation. This is really well explained.

If any of the requests fails for some reason, how will I catch which? And maybe the error given by Doctrine

You can always wrap flush in try / catch blocks and grab elegantly the exceptions that occur when query fails.

 try { $em->flush() }(\Exception $e) { // do stuff throw $e;// re-thrown Exception } 

When using implicit transaction demarcation and an exception occurs during EntityManager # flush (), the transaction is automatically rolled back and EntityManager is closed.

Read more about the topic here .

Update

In the code you presented, if you use flash outside the loop, all delete operations will belong to one transaction. This means that if any of them fails, an exception is thrown and a rollback is issued (all remote operations are performed and they are not stored in the database).

For example: imagine that we have four elements with identifiers 1,2,3,4,5,6 and suppose that deleting 4 is not performed.

First option-> Hidden inner loop: 1,2,3 deleted. 4 the exception fails and ends.

The second option-> Hidden external loop: 4 crash, rollback, not one is deleted and the program ends.

If the behavior you want to achieve is the one shown in case 1, one of the options may be the one you use. However, it is very expensive in terms of performance.

However, there are more efficient solutions: for example, you could use a combination of preRemove / postRemove events to store the identifiers (or any other value) of those objects that were successfully deleted in the flash (although they were not saved because the rollback). You can store them, for example, in a static array belonging to a class (or use a singleton or any other). Then, in the catch clause of the exception, you can use this array to iterate and perform the delete operation for these elements (of course, outside the loop).

Then you can return the array so that the user knows that you actually deleted these entities and false, because there was a problem during the removal process.