Migrate data between tables efficiently

Question

I wrote a very simple quick and easy script to migrate a bunch of data to another table in my database.

Can I accomplish this task without using so many foreach loops?
I realize I'm imputting similar data in each of my loops and it just seems redundant and wrong.

I thought of using array_merge on talkers, pros & anons and I thought that because each of the second loops use the same field, I would write code to retrieve getYI and then write code to determine if it belonged to a talker, anon, or pro.
However I wasn't sure if either of those solutions would make my script any more efficient/better.

Certainly any insight would be appreciated. Thanks in advance!

And if it helps I'm using Symfony 2.3.

public function migratingDataScript()
{
 $em = $this->getDoctrine()->getManager();
 $talkers = $em->getRepository('AppBundle:Talker')->findAll();
 $anons = $em->getRepository('AppBundle:Anon')->findAll();
 $pros = $em->getRepository('AppBundle:Pro')->findAll();
 foreach ($talkers as $talker) {
 echo "Talker: ".$talker->getId()."<br>";
 foreach ($talker->getYI() as $year => $status) {
 $rounds = $em->getRepository('AppBundle:Round')->findByYear($year);
 foreach ($rounds as $round) {
 $newTalkerInvitation = new Invitation($round);
 $newTalkerInvitation->setStatus($status);
 $newTalkerInvitation->setTalker($talker);
 // $newTalkerInvitation->setNumAt(); No Data For Talker
 // $newTalkerInvitation->setStart(); No Data For Talker
 $em->persist($newTalkerInvitation);
 }
 }
 }//Ends Talker Migration
 foreach ($anons as $anon) {
 echo "Anon: ".$anon->getId()."<br>";
 foreach ($anon->getYI() as $year => $status) {
 $rounds = $em->getRepository('AppBundle:Round')->findByYear($year);
 foreach ($rounds as $round) {
 $newAnonInvitation = new Invitation($round);
 $newAnonInvitation->setStatus($status);
 $newAnonInvitation->setAnon($anon);
 // $newAnonInvitation->setNumAt(); No Data For Anon
 // $newAnonInvitation->setStart(); No Data For Anon
 $em->persist($newAnonInvitation);
 }
 }
 }//Ends Anon Migration
 foreach ($pros as $pro) {
 echo "Pro: ".$pro->getId()."<br>";
 foreach ($pro->getYI() as $year => $status) {
 $rounds = $em->getRepository('AppBundle:Round')->findByYear($year);
 foreach ($rounds as $round) {
 $newProInvitation = new Invitation($round);
 $newProInvitation->setStatus($status);
 $newProInvitation->setPro($pro);
 if(is_null($pro->getNumOfAttempts())){
 $newProInvitation->setNumAt('0');
 }else {
 $newProInvitation->setNumAt($pro->getNumOfAttempts());
 }
 // $newProInvitation->setStart(); No Data For Pro
 $em->persist($newProInvitation);
 }
 }
 }//Ends Pro Migration
 $em->flush();
 echo "Completed.";
 die;
}

• colburton: I recommend you take a look at iterate() (docs.doctrine-project.org/projects/doctrine-orm/en/latest/…) instead of findAll(). This can save you a lot of memory.

Answer 1

Can I accomplish this task without using so many foreach loops?

Depends.

If you want to stick with database-agnostic abstraction such as Doctrine, there is no way to avoid loops.

But if you want an efficient solution, you can write a database-specific query to copy all records in a single query. For example, for mysql it would be something like

INSERT INTO invitation (field1, field2, status, talker) 
 SELECT field1, field2, ?, ? FROM talker

and then bind $status and $talker to this query.