Version20150708120022.php (4198B)
1 <?php 2 3 namespace PartKeepr\CoreBundle\DoctrineMigrations; 4 5 use Doctrine\DBAL\Schema\Schema; 6 7 /** 8 * Fixes the category trees due to the migration of doctrine2-nestedset to DoctrineExtensions. 9 */ 10 class Version20150708120022 extends BaseMigration 11 { 12 /** 13 * @param Schema $schema 14 */ 15 public function up(Schema $schema) 16 { 17 $this->performDatabaseUpgrade(); 18 19 $this->fixTree('PartCategory'); 20 $this->fixTree('FootprintCategory'); 21 $this->fixTree('StorageLocationCategory'); 22 23 $this->createRootNode('partkeepr.part.category_service'); 24 $this->createRootNode('partkeepr.storage_location.category_service'); 25 $this->createRootNode('partkeepr.footprint.category_service'); 26 } 27 28 protected function createRootNode($service) 29 { 30 $this->getContainer()->get($service)->ensureRootNodeExists(); 31 } 32 33 /** 34 * Fixes the tree for a given table due to the migration of doctrine2-nestedset to DoctrineExtensions. 35 * 36 * @param string $table The table name to fix 37 * 38 * @throws \Doctrine\DBAL\DBALException 39 */ 40 public function fixTree($table) 41 { 42 $nodes = $this->getNodeIds($table); 43 44 $queryBuilder = $this->connection->createQueryBuilder(); 45 $queryBuilder->update($table) 46 ->set('parent_id', ':parent') 47 ->set('root', ':root') 48 ->set('lvl', ':level') 49 ->where('id = :id'); 50 51 foreach ($nodes as $node) { 52 $parent = $this->fetchParent($table, $node['id']); 53 $level = $this->getLevel($table, $node['id']); 54 55 if ($parent !== false) { 56 $this->connection->executeUpdate( 57 $queryBuilder->getSQL(), 58 [ 59 ':parent' => $parent, 60 ':id' => $node['id'], 61 ':level' => $level, 62 ':root' => 1, 63 ] 64 ); 65 } else { 66 $this->connection->executeUpdate( 67 $queryBuilder->getSQL(), 68 [ 69 ':parent' => null, 70 ':id' => $node['id'], 71 ':root' => 1, 72 ':level' => 0, 73 ] 74 ); 75 } 76 } 77 } 78 79 /** 80 * Fetches the parent node for a table and ID. 81 * 82 * @param $table 83 * @param $id 84 * 85 * @return mixed 86 */ 87 public function fetchParent($table, $id) 88 { 89 $queryBuilder = $this->connection->createQueryBuilder(); 90 91 $queryBuilder->select('parent.id') 92 ->from($table, 'node') 93 ->from($table, 'parent') 94 ->where('parent.lft < node.lft') 95 ->andWhere('parent.rgt > node.rgt') 96 ->andWhere('node.id = :nodeid') 97 ->orderBy('parent.rgt - parent.lft') 98 ->setMaxResults(1); 99 100 return $this->connection->fetchColumn($queryBuilder->getSQL(), [':nodeid' => $id], 0); 101 } 102 103 /** 104 * Returns the node IDs for the table. 105 * 106 * @param $table 107 * 108 * @return array 109 */ 110 public function getNodeIds($table) 111 { 112 $qb = $this->connection->createQueryBuilder(); 113 114 $qb->select('id') 115 ->from($table) 116 ->orderBy('id', 'ASC'); 117 118 return $this->connection->fetchAll($qb->getSQL()); 119 } 120 121 /** 122 * Returns the level for a given table and ID. 123 * 124 * @param $table 125 * @param $id 126 * 127 * @return mixed 128 */ 129 public function getLevel($table, $id) 130 { 131 $qb = $this->connection->createQueryBuilder(); 132 133 $qb->select('COUNT(*) AS level') 134 ->from($table, 'node') 135 ->from($table, 'parent') 136 ->where('node.lft > parent.lft') 137 ->andWhere('node.lft < parent.rgt') 138 ->andWhere('node.id = :nodeid'); 139 140 return $this->connection->fetchAssoc($qb->getSQL(), [':nodeid' => $id])['level']; 141 } 142 143 /** 144 * @param Schema $schema 145 */ 146 public function down(Schema $schema) 147 { 148 // this down() migration is auto-generated, please modify it to your needs 149 } 150 }