Doctrine2 with codeigniter foreign key insertion

Question

I follow the database schema -

Now the department tables, years and tables are already filled with information.

Now I need to insert student data. Student data must be imported from the xls file (the score is being imported and processed). As you can see from the diagram, the columns from the student_data table are for year_id , department_di and division_id . Therefore, when I insert, I need their ID field, since xls has the corresponding name values.

Therefore, I have to get the corresponding identifier depending on the column value for each student. Thus, this introduces 3 queries that need to be fired to insert one record into the student table. Like this -

 forloop(...): $studentData = new Entities\StudentData(); $year = $this->em->getRepository("Entities\Year")->findBy(array('year_name' => $this->year[$i])); $department = $this->em->getRepository("Entities\Department")->findBy(array('department_name' => $this->branch[$i])); $division = $this->em->getRepository("Entities\Division")->findBy(array('division_name'=>$this->division[$i])); $studentData->setYear($year[0]); $studentData->setDepartment($department[0]); $studentData->setDivision($division[0]); //other data . . . . . $this->em->persist($studentData); endforloop(); $this->em->flush(); $this->em->clear(); 

As you can see, I should get an identifier with a loop for each department, year, and division. Suppose I import 100 student lists, so it eventually runs 300 requests to get these 3 ID fields.

Can I get identifiers for the year, department and unit on their behalf directly when inserting data? I am new to doctrine and I do not know how to do it.

---

Update If the question is unclear, please let me know. I can update it in more detail or restructure it.

Answer 1

Optimization

You can optimize your process without using Doctrine result caches:

First create a map of years for your identifiers, for example:

 $yearsMap = array(); $q = $em->createQuery('SELECT y.id, y.year_name FROM Entities\Year y'); foreach ($q->getScalarResult() as $row) { $yearsMap[$row['year_name']] = $row['id']; } 

Also create a department map for their identifiers and divide them into identifiers. This will result in 3 (light) queries. The best place to put this code in a (custom) repository.

Then you can run your loop, but “get” the actual objects like this:

 $year = $this->em->getReference('Entities\Year', $yearsMap[$this->year[$i]]); $department = $this->em->getReference('Entities\Department', $departmentsMap[$this->branch[$i]]); $division = $this->em->getReference('Entities\Division', $divisionsMap[$this->division[$i]]); 

I say "get" because getReference() actually creates a proxy (if it has not already been loaded by the manager entity, but in this case it probably is not). This proxy server has not yet been loaded, so requests are not executed here.

The rest of the code does not need to be changed.

Now when flush() is called, Doctrine will load every single year / department / division only once. This can lead to several requests, depending on how many different years / departments / units are used. Therefore, if all 100 students use different years / departments / departments, you will receive 403 requests (3 for maps, 300 for downloading proxies, 100 for inserting students). But if all 100 students use the same year / department / department, you will receive a total of 106 requests (3 for maps, 3 for downloading proxies, 100 for inserting students).

Optimize another way

Another way is to use the names you collected to get all the objects you need:

 $q = $em->createQuery('SELECT y FROM Entities\Year y INDEX BY y.year_name WHERE y.year_name IN(:years)'); $q->setParameter('years', $yearNames); $yearsMap = $q->getResult(); 

Now you have all the Year objects you need with just one query. You can do the same for departments and departments.

Also pay attention to INDEX BY in the DQL statement: this will make sure that you get an array with year_name as the key, and the object as the value. You can use this directly in your loop like this:

 $year = $yearsMap[$this->year[$i]]; $department = $departmentsMap[$this->branch[$i]]; $division = $divisionsMap[$this->division[$i]]; 

The final result for 100 students will always be 103 requests (3 for cards, 100 for inserting students).

Cache

If you need to run this cycle relatively often, and it creates a database, it makes sense to use the Doctrine result cache . A few things to note:

getReference() does not support caching of results (yet), and the result cache is not used automatically. Therefore, I suggest you add something like this to the repository:

 public function findOneYearByName($name) { $q = $em->createQuery('SELECT y FROM Entities\Year y WHERE y.year_name = :year'); $q->setParameter('year', $name); $q->useResultCache(true); return $q->getSingleResult(); } 

You probably want to tune the result cache, see the docs for more on this.

Another note: the result cache caches the result obtained from the database before it is hydrated. Therefore, even when using result caches, actual objects are hydrated every time. Nevertheless, I still recommend using cards, but implemented a little differently:

 $yearsMap = array(); $departmentsMap = array(); $divisionsMap = array(); forloop (...): if (!isset($yearsMap[$this->year[$i]])) { $yearsMap[$this->year[$i]] = $this->em->getRepository('Entities\Year')->findOneYearByName($this->year[$i]); } if (!isset($departmentsMap[$this->branch[$i]])) { $departmentsMap[$this->branch[$i]] = $this->em->getRepository('Entities\Department')->findOneDepartmentByName($this->branch[$i]); } if (!isset($divisionsMap[$this->division[$i]])) { $divisionsMap[$this->division[$i]] = $this->em->getRepository('Entities\Division')->findOneDivisionByName($this->division[$i]); } $year = $yearsMap[$this->year[$i]]; $department = $departmentsMap[$this->branch[$i]]; $division = $divisionsMap[$this->division[$i]]; 

This ensures that every single year / department / division is hydrated only once.

PS: Using the result cache for "Optimize another path" will not work as efficiently, because the names of years / departments / units may be different each time the cycle starts. With every name change, the queries change, and the cached results cannot be used.

DBAL

Can I get identifiers for the year, department and division on their behalf directly when inserting data?

You can, but you will not use ORM, but only DBAL. You basically do this:

 $connection = $em->getConnection(); $statement = $conn->executeQuery('insert query', array('parameter1', 'etc')); $statement->execute(); 

I doubt it will be more efficient, because MySQL (or any other provider that you use) will still execute these 3 (auxiliary) queries for each insertion, they just don't "go through the wire." And you do not get any help from ORM, like association management, etc.

However, you can find everything on the topic here .

Answer 2

Have you checked if 300 queries are running? Because this, of course, should not, if all students do not have different years, departments and , which seems unlikely. And if that were so, then 300 queries would at least be required with or without Doctrine, with the exception of other optimizations.

The good thing is that Doctrine is more than just a fancy way to access objects — it's a complete database abstraction layer that provides many more services, such as a full-blown object cache . Next line:

 $year = $this->em->getRepository("Entities\Year")->findBy(array('year_name' => $this->year[$i])); 

This should complete no more than 1 request in one given year - after that the result will be saved, fully hydrated, in the internal Doctrine caches inside the manager object . And it is assumed that you are using the backup MemoryCache, which is enabled by default, unless you specify anything else that is cached with only one request. If you install APC, Memcache, Memcached, or even FilesystemCache ( choose one! ), The results will most likely be cached for several requests.

So, in short, you present a problem that is missing there, or is easily alleviated with a few simple configuration calls . If we are not talking about a hypothetical case where all years, departments and units are unique, then you really initiate 300 requests. The problem in this case, however, is not the Doctrine - it simply fulfills what you command it, separately receiving 300 unique objects. In this case, no one is stopping you from writing your own smart code around Doctrine, for example:

 // Build cache before loop $years = []; foreach($this->em->getRepository("Entities\Year")->findAll() as $year) $years[$year->getYearName()] = $year; // Now loop much faster because everything already indexed forloop(...) : $studentData = new Entities\StudentData(); $studentData->setYear($years[$this->year[$i]]); endforloop; 

And suddenly you have 1 “expensive” request instead of 100 a bit cheaper. Doctrine is a convenience to make a lot of database-related coding more subtle and more structured, it does not prohibit intelligent performance-oriented coding. After all, you are still an encoder, and Doctrine is just one tool in your belt that you can use as you wish.