52574

Symfony2 / Doctrine queries in loops

Question:

I'm cleaning up some old code written by someone else because we're having problems with time-outs, especially with customers who are pushing the limits of our system.

I know this is an anti-pattern, and the code is full of variations of this:

$userIDs = [100,101,107,208, ...]; // tons of users, but not all users in a company. $companyID = 4356; foreach ($userIDs as $id) { $user = $em->getRepository('AdminBundle:Users') ->getUser($id, $companyId); $user->removeGroup($group); $em->persist($user); $em->flush(); }

It just causes a query for every user, which makes the server time out (profiler shows hundreds of queries). Increasing the timeouts does work, but that solution doesn't scale well... and causes me to take more coffee breaks than is healthy.

What would be the correct, efficient, symfony-ey way to rewrite this into a single update query?

Answer1:

Two possibilities :

<ol><li>

using a query builder with a whereIn statement , bound to the array of users id and a andWhere with the company id

</li> <li>

if the goal is to do a DELETE, and you don't make any other use of the entity after that, you can use a crafted DQL request.

</li> </ol>

if needed I can edit and put some more helpers.

Recommend

  • Symfony 2.3 / Doctrine2 personal translations not working in Sonata Admin with TranslatedFieldType.p
  • Sonata Admin Bundle - Form type: sonata_type_collection - custom template?
  • show sonata admin menu in every page if the admin is logged in
  • Symfony2 SonataAdminBundle Password field encryption
  • Populate Multiple Forms Symfony2
  • Get form values in Symfony2 controller , Child does not exist
  • I am sending dictionary in json and its have two array and multiple value but i am getting response
  • Mongodb Aggregate using $group twice
  • Entity Framework returning different data then DB query
  • jQuery autocomplete not displaying text values but populates elements
  • How to return an array not an object with doctrine findOneBy method in symfony2?
  • Write “NOT IN” in Doctrine Query Language
  • FOSRestBundle: The controller must return a response (Array()) given
  • Symfony2 and MVC - Is extend controller a good practice?
  • How to handle empty space in url when downloading image from web?
  • How to Save JSON data to SQL server database in C#?
  • Does CSS support inheritance [duplicate]
  • CSS - Cannot get one spanned style to override another inherited style and align left
  • Extract All Possible Paths from Expression-Tree and evaluate them to hold TRUE
  • XSLT foreach repeating nodes to flat
  • List images(01.png) and descriptions(01.txt) from directory
  • Is there a way to do normal logging with EureakLog?
  • PHP buffered output depending on server setting?
  • D3 nodes and links from JSON with nested arrays of children
  • How to make a tree having multiple type of nodes and each node can have multiple child nodes in java
  • Cassandra Data Model
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • Codeigniter doesn't let me update entry, because some fields must be unique
  • How to stop GridView from loading again when I press back button?
  • Authorize attributes not working in MVC 4
  • Busy indicator not showing up in wpf window [duplicate]
  • How to get NHibernate ISession to cache entity not retrieved by primary key
  • Observable and ngFor in Angular 2
  • How to Embed XSL into XML
  • UserPrincipal.Current returns apppool on IIS
  • Conditional In-Line CSS for IE and Others?
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF