/** * Migrate data to plugin database from core table * * @param EntityManager $em * @param OutputInterface $output * * @return void */ public function migrateData($em, $output) { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Newscoop\\CommunityTickerBundle\\Entity\\CommunityTickerEvent', 'e'); $rsm->addFieldResult('e', 'id', 'id'); $rsm->addFieldResult('e', 'event', 'event'); $rsm->addFieldResult('e', 'params', 'params'); $rsm->addFieldResult('e', 'created', 'created'); $rsm->addJoinedEntityResult('Newscoop\\Entity\\User', 'u', 'e', 'user'); $rsm->addFieldResult('u', 'Id', 'id'); $query = $em->createNativeQuery('SELECT e.id, e.event, e.params, e.created, u.Id FROM community_ticker_event e ' . 'LEFT JOIN liveuser_users u ON u.id = e.user_id', $rsm); $events = $query->getArrayResult(); foreach ($events as $key => $event) { $user = $em->getRepository('Newscoop\\Entity\\User')->findOneBy(array('id' => $event['user']['id'])); $existingEvent = $em->getRepository('Newscoop\\CommunityTickerBundle\\Entity\\CommunityTickerEvent')->findOneBy(array('created' => $event['created'], 'params' => $event['params'])); if (!$existingEvent) { $newEvent = new CommunityTickerEvent(); $newEvent->setEvent($event['event']); $newEvent->setParams($event['params'] != '[]' ? json_decode($event['params'], true) : array()); $newEvent->setCreated($event['created']); $newEvent->setIsActive(true); if ($user) { $newEvent->setUser($user); } $em->persist($newEvent); } } $em->flush(); $output->writeln('<info>Data migrated to plugin table!</info>'); $output->writeln('<info>Removing old table...</info>'); }
public function testIssue() { $config = $this->_em->getConfiguration(); $config->addEntityNamespace('MyNamespace', 'Doctrine\\Tests\\ORM\\Functional\\Ticket'); $user = new DDC2256User(); $user->name = 'user'; $group = new DDC2256Group(); $group->name = 'group'; $user->group = $group; $this->_em->persist($user); $this->_em->persist($group); $this->_em->flush(); $this->_em->clear(); $sql = 'SELECT u.id, u.name, g.id as group_id, g.name as group_name FROM ddc2256_users u LEFT JOIN ddc2256_groups g ON u.group_id = g.id'; // Test ResultSetMapping. $rsm = new ResultSetMapping(); $rsm->addEntityResult('MyNamespace:DDC2256User', 'u'); $rsm->addFieldResult('u', 'id', 'id'); $rsm->addFieldResult('u', 'name', 'name'); $rsm->addJoinedEntityResult('MyNamespace:DDC2256Group', 'g', 'u', 'group'); $rsm->addFieldResult('g', 'group_id', 'id'); $rsm->addFieldResult('g', 'group_name', 'name'); $this->_em->createNativeQuery($sql, $rsm)->getResult(); // Test ResultSetMappingBuilder. $rsm = new ResultSetMappingBuilder($this->_em); $rsm->addRootEntityFromClassMetadata('MyNamespace:DDC2256User', 'u'); $rsm->addJoinedEntityFromClassMetadata('MyNamespace:DDC2256Group', 'g', 'u', 'group', array('id' => 'group_id', 'name' => 'group_name')); $this->_em->createNativeQuery($sql, $rsm)->getResult(); }
/** * Walks down a SelectClause AST node, thereby generating the appropriate SQL. * * @param $selectClause * @return string The SQL. */ public function walkSelectClause($selectClause) { $sql = 'SELECT ' . ($selectClause->isDistinct ? 'DISTINCT ' : '') . implode(', ', array_map(array($this, 'walkSelectExpression'), $selectClause->selectExpressions)); $addMetaColumns = !$this->_query->getHint(Query::HINT_FORCE_PARTIAL_LOAD) && $this->_query->getHydrationMode() == Query::HYDRATE_OBJECT || $this->_query->getHydrationMode() != Query::HYDRATE_OBJECT && $this->_query->getHint(Query::HINT_INCLUDE_META_COLUMNS); foreach ($this->_selectedClasses as $dqlAlias => $class) { // Register as entity or joined entity result if ($this->_queryComponents[$dqlAlias]['relation'] === null) { $this->_rsm->addEntityResult($class->name, $dqlAlias); } else { $this->_rsm->addJoinedEntityResult($class->name, $dqlAlias, $this->_queryComponents[$dqlAlias]['parent'], $this->_queryComponents[$dqlAlias]['relation']->sourceFieldName); } if ($class->isInheritanceTypeSingleTable() || $class->isInheritanceTypeJoined()) { // Add discriminator columns to SQL $rootClass = $this->_em->getClassMetadata($class->rootEntityName); $tblAlias = $this->getSqlTableAlias($rootClass->primaryTable['name'], $dqlAlias); $discrColumn = $rootClass->discriminatorColumn; $columnAlias = $this->getSqlColumnAlias($discrColumn['name']); $sql .= ", {$tblAlias}." . $discrColumn['name'] . ' AS ' . $columnAlias; $columnAlias = $this->_platform->getSQLResultCasing($columnAlias); $this->_rsm->setDiscriminatorColumn($dqlAlias, $columnAlias); $this->_rsm->addMetaResult($dqlAlias, $this->_platform->getSQLResultCasing($columnAlias), $discrColumn['fieldName']); // Add foreign key columns to SQL, if necessary if ($addMetaColumns) { //FIXME: Include foreign key columns of child classes also!!?? foreach ($class->associationMappings as $assoc) { if ($assoc->isOwningSide && $assoc->isOneToOne()) { if (isset($class->inheritedAssociationFields[$assoc->sourceFieldName])) { $owningClass = $this->_em->getClassMetadata($class->inheritedAssociationFields[$assoc->sourceFieldName]); $sqlTableAlias = $this->getSqlTableAlias($owningClass->primaryTable['name'], $dqlAlias); } else { $sqlTableAlias = $this->getSqlTableAlias($class->primaryTable['name'], $dqlAlias); } foreach ($assoc->targetToSourceKeyColumns as $srcColumn) { $columnAlias = $this->getSqlColumnAlias($srcColumn); $sql .= ", {$sqlTableAlias}." . $srcColumn . ' AS ' . $columnAlias; $columnAlias = $this->_platform->getSQLResultCasing($columnAlias); $this->_rsm->addMetaResult($dqlAlias, $this->_platform->getSQLResultCasing($columnAlias), $srcColumn); } } } } } else { // Add foreign key columns to SQL, if necessary if ($addMetaColumns) { $sqlTableAlias = $this->getSqlTableAlias($class->primaryTable['name'], $dqlAlias); foreach ($class->associationMappings as $assoc) { if ($assoc->isOwningSide && $assoc->isOneToOne()) { foreach ($assoc->targetToSourceKeyColumns as $srcColumn) { $columnAlias = $this->getSqlColumnAlias($srcColumn); $sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias; $columnAlias = $this->_platform->getSQLResultCasing($columnAlias); $this->_rsm->addMetaResult($dqlAlias, $this->_platform->getSQLResultCasing($columnAlias), $srcColumn); } } } } } } return $sql; }
/** * @group DDC-117 */ public function testIndexByMetadataColumn() { $this->_rsm->addEntityResult('Doctrine\\Tests\\Models\\Legacy\\LegacyUser', 'u'); $this->_rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\Legacy', 'lu', 'u', '_references'); $this->_rsm->addMetaResult('lu', '_source', '_source', true); $this->_rsm->addMetaResult('lu', '_target', '_target', true); $this->_rsm->addIndexBy('lu', '_source'); $this->assertTrue($this->_rsm->hasIndexBy('lu')); }
public function getOrgaDispo($orga_id, $plage_id) { $rsm = new ResultSetMapping(); $rsm->addEntityResult('AssoMakerPHPMBundle:Disponibilite', 'd'); $rsm->addFieldResult('d', 'id', 'id'); $rsm->addFieldResult('d', 'debut', 'debut'); $rsm->addFieldResult('d', 'fin', 'fin'); $rsm->addJoinedEntityResult('AssoMakerPHPMBundle:Creneau', 'c', 'd', 'creneaux'); $rsm->addFieldResult('c', 'cid', 'id'); $rsm->addFieldResult('c', 'cd', 'debut'); $rsm->addFieldResult('c', 'cf', 'fin'); $rsm->addJoinedEntityResult('AssoMakerPHPMBundle:PlageHoraire', 'p', 'c', 'plageHoraire'); $rsm->addFieldResult('p', 'pi', 'id'); $rsm->addJoinedEntityResult('AssoMakerPHPMBundle:Tache', 't', 'p', 'tache'); $rsm->addFieldResult('t', 'ti', 'id'); $rsm->addFieldResult('t', 'tn', 'nom'); $rsm->addFieldResult('t', 'tl', 'lieu'); $rsm->addJoinedEntityResult('AssoMakerPHPMBundle:GroupeTache', 'g', 't', 'groupeTache'); $rsm->addFieldResult('g', 'ge', 'id'); // bien garder id, le referredBY name $sql = 'SELECT d.id, d.debut, d.fin, c.id AS cid, c.debut AS cd, c.fin AS cf, p.id AS pi, t.id AS ti, t.nom AS tn, t.lieu AS tl, g.equipe_id AS ge FROM Disponibilite d LEFT OUTER JOIN Creneau c ON c.disponibilite_id = d.id LEFT OUTER JOIN PlageHoraire p ON c.plageHoraire_id = p.id LEFT OUTER JOIN Tache t ON p.tache_id = t.id LEFT OUTER JOIN GroupeTache g ON t.groupetache_id = g.id WHERE d.orga_id = ?'; if ($plage_id !== '') { $pref = json_decode($this->getEntityManager()->getRepository('AssoMakerPHPMBundle:Config')->findOneByField('manifestation_plages')->getValue(), TRUE); $plage = $pref[$plage_id]; $fin = $plage["fin"]; $debut = $plage["debut"]; $sql .= " AND DATE(d.debut) <= DATE('{$fin}') AND DATE(d.fin) >= DATE('{$debut}')"; } $query = $this->getEntityManager()->createNativeQuery($sql, $rsm); $query->setParameter(1, $orga_id); // PDO \o/ return $query->getArrayResult(); }
public function getNextBackupForFridaySubscriptions() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('STX\\CroissantsBundle\\Entity\\Friday_Subscriptions', 'fs'); $rsm->addFieldResult('fs', 'user_id', 'id'); $rsm->addJoinedEntityResult('STX\\UserBundle\\Entity\\User', 'cu', 'fs', 'user'); $rsm->addFieldResult('cu', 'id', 'id'); $rsm->addFieldResult('cu', 'username', 'username'); $rsm->addFieldResult('cu', 'email', 'email'); $sql = 'select fs.backup_user_id, cu.id, cu.username, cu.email' . ' from friday_subscriptions fs ' . ' left join croissants_user cu on cu.id = fs.backup_user_id ' . ' where TIMESTAMPDIFF(DAY, NOW(), fs.date) BETWEEN 0 AND 6 ' . ' AND cu.alert_email = 1 ' . ' AND cu.enabled = 1 ' . ' AND (TIMESTAMPDIFF(DAY, NOW(), fs.date) + 1) = cu.alert_days ' . ' AND fs.user_id IS NULL ' . ' ORDER BY fs.date DESC ' . ' LIMIT 1'; $query = $this->_em->createNativeQuery($sql, $rsm); $results = $query->getResult(); return $results; }
/** * [romanb: 2000 rows => 3.1 seconds] * * MAXIMUM TIME: 4 seconds */ public function testMixedQueryFetchJoinObjectHydrationPerformance() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsPhonenumber', 'p', 'u', $this->_em->getClassMetadata('Doctrine\\Tests\\Models\\CMS\\CmsUser')->getAssociationMapping('phonenumbers')); $rsm->addFieldResult('u', 'u__id', 'id'); $rsm->addFieldResult('u', 'u__status', 'status'); $rsm->addFieldResult('u', 'u__username', 'username'); $rsm->addFieldResult('u', 'u__name', 'name'); $rsm->addScalarResult('sclr0', 'nameUpper'); $rsm->addFieldResult('p', 'p__phonenumber', 'phonenumber'); // Faked result set $resultSet = array(array('u__id' => '1', 'u__status' => 'developer', 'u__username' => 'romanb', 'u__name' => 'Roman', 'sclr0' => 'ROMANB', 'p__phonenumber' => '42'), array('u__id' => '1', 'u__status' => 'developer', 'u__username' => 'romanb', 'u__name' => 'Roman', 'sclr0' => 'ROMANB', 'p__phonenumber' => '43'), array('u__id' => '2', 'u__status' => 'developer', 'u__username' => 'romanb', 'u__name' => 'Roman', 'sclr0' => 'JWAGE', 'p__phonenumber' => '91')); for ($i = 4; $i < 2000; ++$i) { $resultSet[] = array('u__id' => $i, 'u__status' => 'developer', 'u__username' => 'jwage', 'u__name' => 'Jonathan', 'sclr0' => 'JWAGE' . $i, 'p__phonenumber' => '91'); } $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\ObjectHydrator($this->_em); $this->setMaxRunningTime(4); $result = $hydrator->hydrateAll($stmt, $rsm); }
/** * Gets the SQL fragment with the list of columns to select when querying for * an entity in this persister. * * Subclasses should override this method to alter or change the select column * list SQL fragment. Note that in the implementation of BasicEntityPersister * the resulting SQL fragment is generated only once and cached in {@link selectColumnListSql}. * Subclasses may or may not do the same. * * @return string The SQL fragment. */ protected function getSelectColumnsSQL() { if ($this->selectColumnListSql !== null) { return $this->selectColumnListSql; } $columnList = array(); $this->rsm = new Query\ResultSetMapping(); $this->rsm->addEntityResult($this->class->name, 'r'); // r for root // Add regular columns to select list foreach ($this->class->fieldNames as $field) { $columnList[] = $this->getSelectColumnSQL($field, $this->class); } $this->selectJoinSql = ''; $eagerAliasCounter = 0; foreach ($this->class->associationMappings as $assocField => $assoc) { $assocColumnSQL = $this->getSelectColumnAssociationSQL($assocField, $assoc, $this->class); if ($assocColumnSQL) { $columnList[] = $assocColumnSQL; } if ( ! (($assoc['type'] & ClassMetadata::TO_ONE) && ($assoc['fetch'] == ClassMetadata::FETCH_EAGER || !$assoc['isOwningSide']))) { continue; } $eagerEntity = $this->em->getClassMetadata($assoc['targetEntity']); if ($eagerEntity->inheritanceType != ClassMetadata::INHERITANCE_TYPE_NONE) { continue; // now this is why you shouldn't use inheritance } $assocAlias = 'e' . ($eagerAliasCounter++); $this->rsm->addJoinedEntityResult($assoc['targetEntity'], $assocAlias, 'r', $assocField); foreach ($eagerEntity->fieldNames as $field) { $columnList[] = $this->getSelectColumnSQL($field, $eagerEntity, $assocAlias); } foreach ($eagerEntity->associationMappings as $eagerAssocField => $eagerAssoc) { $eagerAssocColumnSQL = $this->getSelectColumnAssociationSQL( $eagerAssocField, $eagerAssoc, $eagerEntity, $assocAlias ); if ($eagerAssocColumnSQL) { $columnList[] = $eagerAssocColumnSQL; } } $association = $assoc; $joinCondition = array(); if ( ! $assoc['isOwningSide']) { $eagerEntity = $this->em->getClassMetadata($assoc['targetEntity']); $association = $eagerEntity->getAssociationMapping($assoc['mappedBy']); } $joinTableAlias = $this->getSQLTableAlias($eagerEntity->name, $assocAlias); $joinTableName = $this->quoteStrategy->getTableName($eagerEntity, $this->platform); if ($assoc['isOwningSide']) { $tableAlias = $this->getSQLTableAlias($association['targetEntity'], $assocAlias); $this->selectJoinSql .= ' ' . $this->getJoinSQLForJoinColumns($association['joinColumns']); foreach ($association['joinColumns'] as $joinColumn) { $sourceCol = $this->quoteStrategy->getJoinColumnName($joinColumn, $this->class, $this->platform); $targetCol = $this->quoteStrategy->getReferencedJoinColumnName($joinColumn, $this->class, $this->platform); $joinCondition[] = $this->getSQLTableAlias($association['sourceEntity']) . '.' . $sourceCol . ' = ' . $tableAlias . '.' . $targetCol; } // Add filter SQL if ($filterSql = $this->generateFilterConditionSQL($eagerEntity, $tableAlias)) { $joinCondition[] = $filterSql; } } else { $this->selectJoinSql .= ' LEFT JOIN'; foreach ($association['joinColumns'] as $joinColumn) { $sourceCol = $this->quoteStrategy->getJoinColumnName($joinColumn, $this->class, $this->platform); $targetCol = $this->quoteStrategy->getReferencedJoinColumnName($joinColumn, $this->class, $this->platform); $joinCondition[] = $this->getSQLTableAlias($association['sourceEntity'], $assocAlias) . '.' . $sourceCol . ' = ' . $this->getSQLTableAlias($association['targetEntity']) . '.' . $targetCol; } } $this->selectJoinSql .= ' ' . $joinTableName . ' ' . $joinTableAlias . ' ON '; $this->selectJoinSql .= implode(' AND ', $joinCondition); } $this->selectColumnListSql = implode(', ', $columnList); return $this->selectColumnListSql; }
public function testJoinedOneToOneNativeQuery() { $user = new CmsUser(); $user->name = 'Roman'; $user->username = '******'; $user->status = 'dev'; $addr = new CmsAddress(); $addr->country = 'germany'; $addr->zip = 10827; $addr->city = 'Berlin'; $user->setAddress($addr); $this->_em->persist($user); $this->_em->flush(); $this->_em->clear(); $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $rsm->addFieldResult('u', $this->platform->getSQLResultCasing('id'), 'id'); $rsm->addFieldResult('u', $this->platform->getSQLResultCasing('name'), 'name'); $rsm->addFieldResult('u', $this->platform->getSQLResultCasing('status'), 'status'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsAddress', 'a', 'u', 'address'); $rsm->addFieldResult('a', $this->platform->getSQLResultCasing('a_id'), 'id'); $rsm->addFieldResult('a', $this->platform->getSQLResultCasing('country'), 'country'); $rsm->addFieldResult('a', $this->platform->getSQLResultCasing('zip'), 'zip'); $rsm->addFieldResult('a', $this->platform->getSQLResultCasing('city'), 'city'); $query = $this->_em->createNativeQuery('SELECT u.id, u.name, u.status, a.id AS a_id, a.country, a.zip, a.city FROM cms_users u INNER JOIN cms_addresses a ON u.id = a.user_id WHERE u.username = ?', $rsm); $query->setParameter(1, 'romanb'); $users = $query->getResult(); $this->assertEquals(1, count($users)); $this->assertInstanceOf('Doctrine\\Tests\\Models\\CMS\\CmsUser', $users[0]); $this->assertEquals('Roman', $users[0]->name); $this->assertInstanceOf('Doctrine\\ORM\\PersistentCollection', $users[0]->getPhonenumbers()); $this->assertFalse($users[0]->getPhonenumbers()->isInitialized()); $this->assertInstanceOf('Doctrine\\Tests\\Models\\CMS\\CmsAddress', $users[0]->getAddress()); $this->assertTrue($users[0]->getAddress()->getUser() == $users[0]); $this->assertEquals('germany', $users[0]->getAddress()->getCountry()); $this->assertEquals(10827, $users[0]->getAddress()->getZipCode()); $this->assertEquals('Berlin', $users[0]->getAddress()->getCity()); }
public function testChainedJoinWithEmptyCollections() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsArticle', 'a', 'u', 'articles'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsComment', 'c', 'a', 'comments'); $rsm->addFieldResult('u', 'u__id', 'id'); $rsm->addFieldResult('u', 'u__status', 'status'); $rsm->addFieldResult('a', 'a__id', 'id'); $rsm->addFieldResult('a', 'a__topic', 'topic'); $rsm->addFieldResult('c', 'c__id', 'id'); $rsm->addFieldResult('c', 'c__topic', 'topic'); // Faked result set $resultSet = array(array('u__id' => '1', 'u__status' => 'developer', 'a__id' => null, 'a__topic' => null, 'c__id' => null, 'c__topic' => null), array('u__id' => '2', 'u__status' => 'developer', 'a__id' => null, 'a__topic' => null, 'c__id' => null, 'c__topic' => null)); $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\ObjectHydrator($this->_em); $result = $hydrator->hydrateAll($stmt, $rsm, array(Query::HINT_FORCE_PARTIAL_LOAD => true)); $this->assertEquals(2, count($result)); $this->assertTrue($result[0] instanceof CmsUser); $this->assertTrue($result[1] instanceof CmsUser); $this->assertEquals(0, $result[0]->articles->count()); $this->assertEquals(0, $result[1]->articles->count()); }
public function testFetchJoinCollectionValuedAssociationWithDefaultArrayValue() { $rsm = new ResultSetMapping(); $rsm->addEntityResult(EntityWithArrayDefaultArrayValueM2M::CLASSNAME, 'e1', null); $rsm->addJoinedEntityResult(SimpleEntity::CLASSNAME, 'e2', 'e1', 'collection'); $rsm->addFieldResult('e1', 'a1__id', 'id'); $rsm->addFieldResult('e2', 'e2__id', 'id'); $result = (new \Doctrine\ORM\Internal\Hydration\ObjectHydrator($this->_em))->hydrateAll(new HydratorMockStatement([['a1__id' => '1', 'e2__id' => '1']]), $rsm); $this->assertCount(1, $result); $this->assertInstanceOf(EntityWithArrayDefaultArrayValueM2M::CLASSNAME, $result[0]); $this->assertInstanceOf('Doctrine\\ORM\\PersistentCollection', $result[0]->collection); $this->assertCount(1, $result[0]->collection); $this->assertInstanceOf(SimpleEntity::CLASSNAME, $result[0]->collection[0]); }
/** * Tests that the hydrator does not rely on a particular order of the rows * in the result set. * * DQL: * select c.id, c.position, c.name, b.id, b.position * from \Doctrine\Tests\Models\Forum\ForumCategory c inner join c.boards b * order by c.position asc, b.position asc * * Checks whether the boards are correctly assigned to the categories. * * The 'evil' result set that confuses the object population is displayed below. * * c.id | c.position | c.name | boardPos | b.id | b.category_id (just for clarity) * 1 | 0 | First | 0 | 1 | 1 * 2 | 0 | Second | 0 | 2 | 2 <-- * 1 | 0 | First | 1 | 3 | 1 * 1 | 0 | First | 2 | 4 | 1 */ public function testNewHydrationEntityQueryCustomResultSetOrder() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\Forum\\ForumCategory', 'c'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\Forum\\ForumBoard', 'b', 'c', $this->_em->getClassMetadata('Doctrine\\Tests\\Models\\Forum\\ForumCategory')->getAssociationMapping('boards')); $rsm->addFieldResult('c', 'c__id', 'id'); $rsm->addFieldResult('c', 'c__position', 'position'); $rsm->addFieldResult('c', 'c__name', 'name'); $rsm->addFieldResult('b', 'b__id', 'id'); $rsm->addFieldResult('b', 'b__position', 'position'); // Faked result set $resultSet = array(array('c__id' => '1', 'c__position' => '0', 'c__name' => 'First', 'b__id' => '1', 'b__position' => '0'), array('c__id' => '2', 'c__position' => '0', 'c__name' => 'Second', 'b__id' => '2', 'b__position' => '0'), array('c__id' => '1', 'c__position' => '0', 'c__name' => 'First', 'b__id' => '3', 'b__position' => '1'), array('c__id' => '1', 'c__position' => '0', 'c__name' => 'First', 'b__id' => '4', 'b__position' => '2')); $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\ArrayHydrator($this->_em); $result = $hydrator->hydrateAll($stmt, $rsm); $this->assertEquals(2, count($result)); $this->assertTrue(is_array($result)); $this->assertTrue(is_array($result[0])); $this->assertTrue(is_array($result[1])); $this->assertTrue(isset($result[0]['boards'])); $this->assertEquals(3, count($result[0]['boards'])); $this->assertTrue(isset($result[1]['boards'])); $this->assertEquals(1, count($result[1]['boards'])); }
public function getTacheCreneau($tache_id, $plage_id) { $rsm = new ResultSetMapping(); $rsm->addEntityResult('AssoMakerPHPMBundle:Creneau', 'c'); $rsm->addFieldResult('c', 'id', 'id'); $rsm->addFieldResult('c', 'debut', 'debut'); $rsm->addFieldResult('c', 'fin', 'fin'); $rsm->addMetaResult('c', 'did', 'did'); $rsm->addMetaResult('c', 'eid', 'eid'); $rsm->addMetaResult('c', 'oid', 'oid'); $rsm->addJoinedEntityResult('AssoMakerPHPMBundle:PlageHoraire', 'p', 'c', 'plageHoraire'); $rsm->addFieldResult('p', 'pi', 'id'); $rsm->addJoinedEntityResult('AssoMakerPHPMBundle:Tache', 't', 'p', 'tache'); $rsm->addFieldResult('t', 'ti', 'id'); $rsm->addFieldResult('t', 'tn', 'nom'); $rsm->addFieldResult('t', 'tl', 'lieu'); $sql = 'SELECT c.id, c.debut, c.fin, c.disponibilite_id AS did, c.equipeHint_id AS eid, c.orgaHint_id AS oid, p.id AS pi, t.id AS ti, t.nom AS tn, t.lieu AS tl FROM Creneau c JOIN PlageHoraire p ON c.plageHoraire_id = p.id JOIN Tache t ON p.tache_id = t.id WHERE t.id = ?'; if ($plage_id !== '') { $pref = json_decode($this->getEntityManager()->getRepository('AssoMakerPHPMBundle:Config')->findOneByField('manifestation_plages')->getValue(), TRUE); $plage = $pref[$plage_id]; $fin = $plage["fin"]; $debut = $plage["debut"]; $sql .= " AND DATE(c.debut) <= DATE('{$fin}') AND DATE(c.fin) >= DATE('{$debut}')"; } $sql .= ' GROUP BY c.debut, c.fin, eid, oid, (did IS NOT NULL)'; $query = $this->getEntityManager()->createNativeQuery($sql, $rsm); $query->setParameter(1, $tache_id); // PDO \o/ return $query->getArrayResult(); }
/** * This issue tests if, with multiple joined multiple-valued collections the hydration is done correctly. * * User x Phonenumbers x Groups blow up the resultset quite a bit, however the hydration should correctly assemble those. * * @group DDC-809 */ public function testManyToManyHydration() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $rsm->addFieldResult('u', 'u__id', 'id'); $rsm->addFieldResult('u', 'u__name', 'name'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsGroup', 'g', 'u', 'groups'); $rsm->addFieldResult('g', 'g__id', 'id'); $rsm->addFieldResult('g', 'g__name', 'name'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsPhonenumber', 'p', 'u', 'phonenumbers'); $rsm->addFieldResult('p', 'p__phonenumber', 'phonenumber'); // Faked result set $resultSet = array(array('u__id' => '1', 'u__name' => 'romanb', 'g__id' => '3', 'g__name' => 'TestGroupB', 'p__phonenumber' => 1111), array('u__id' => '1', 'u__name' => 'romanb', 'g__id' => '5', 'g__name' => 'TestGroupD', 'p__phonenumber' => 1111), array('u__id' => '1', 'u__name' => 'romanb', 'g__id' => '3', 'g__name' => 'TestGroupB', 'p__phonenumber' => 2222), array('u__id' => '1', 'u__name' => 'romanb', 'g__id' => '5', 'g__name' => 'TestGroupD', 'p__phonenumber' => 2222), array('u__id' => '2', 'u__name' => 'jwage', 'g__id' => '2', 'g__name' => 'TestGroupA', 'p__phonenumber' => 3333), array('u__id' => '2', 'u__name' => 'jwage', 'g__id' => '3', 'g__name' => 'TestGroupB', 'p__phonenumber' => 3333), array('u__id' => '2', 'u__name' => 'jwage', 'g__id' => '4', 'g__name' => 'TestGroupC', 'p__phonenumber' => 3333), array('u__id' => '2', 'u__name' => 'jwage', 'g__id' => '5', 'g__name' => 'TestGroupD', 'p__phonenumber' => 3333), array('u__id' => '2', 'u__name' => 'jwage', 'g__id' => '2', 'g__name' => 'TestGroupA', 'p__phonenumber' => 4444), array('u__id' => '2', 'u__name' => 'jwage', 'g__id' => '3', 'g__name' => 'TestGroupB', 'p__phonenumber' => 4444), array('u__id' => '2', 'u__name' => 'jwage', 'g__id' => '4', 'g__name' => 'TestGroupC', 'p__phonenumber' => 4444), array('u__id' => '2', 'u__name' => 'jwage', 'g__id' => '5', 'g__name' => 'TestGroupD', 'p__phonenumber' => 4444)); $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\ObjectHydrator($this->_em); $result = $hydrator->hydrateAll($stmt, $rsm, array(Query::HINT_FORCE_PARTIAL_LOAD => true)); $this->assertEquals(2, count($result)); $this->assertContainsOnly('Doctrine\\Tests\\Models\\CMS\\CmsUser', $result); $this->assertEquals(2, count($result[0]->groups)); $this->assertEquals(2, count($result[0]->phonenumbers)); $this->assertEquals(4, count($result[1]->groups)); $this->assertEquals(2, count($result[1]->phonenumbers)); }
/** * Return Contact entities based on a selection SQL using a native SQL query. * * @param SelectionSql $sql * @param bool $toArray * * @return Entity\Contact[] */ public function findContactsBySelectionSQL(SelectionSQL $sql, $toArray = false) { $resultSetMap = new ResultSetMapping(); $resultSetMap->addEntityResult('Contact\\Entity\\Contact', 'c'); $resultSetMap->addJoinedEntityResult('Contact\\Entity\\ContactOrganisation', 'co', 'c', 'contactOrganisation'); $resultSetMap->addFieldResult('c', 'contact_id', 'id'); $resultSetMap->addFieldResult('c', 'email', 'email'); $resultSetMap->addFieldResult('c', 'firstname', 'firstName'); $resultSetMap->addFieldResult('c', 'middlename', 'middleName'); $resultSetMap->addFieldResult('c', 'lastname', 'lastName'); $resultSetMap->addFieldResult('co', 'id', 'contact_organisation_id'); $resultSetMap->addJoinedEntityResult('Organisation\\Entity\\Organisation', 'o', 'co', 'organisation'); $resultSetMap->addJoinedEntityResult('General\\Entity\\Country', 'cy', 'o', 'country'); $resultSetMap->addFieldResult('o', 'organisation', 'organisation'); $resultSetMap->addFieldResult('cy', 'country', 'country'); $query = $this->getEntityManager()->createNativeQuery("SELECT c.contact_id, email, firstname, middlename, lastname, co.contact_organisation_id, o.organisation, cy.country FROM contact c\n LEFT JOIN contact_organisation co ON co.contact_id = c.contact_id\n LEFT JOIN organisation o ON co.organisation_id = o.organisation_id\n LEFT JOIN country cy ON o.country_id = cy.country_id\n WHERE c.contact_id IN (" . $sql->getQuery() . ") AND date_end IS NULL ORDER BY lastName", $resultSetMap); /** * 'c_id' => int 5059 * 'c_email' => string '*****@*****.**' (length=26) * 'c_firstName' => string 'Laila' (length=5) * 'c_middleName' => null * 'c_lastName' => string 'Gide' (length=4) * 'o_organisation' => string 'Thales' (length=6) * 'cy_country' => string 'Thales' (length=6) */ if ($toArray) { return $this->reIndexContactArray($query->getResult(AbstractQuery::HYDRATE_SCALAR)); } else { //Note that the contactOrgansiation is always empty return $query->getResult(); } }
/** * {@inheritdoc} */ public function walkSelectClause($selectClause) { $sql = 'SELECT ' . ($selectClause->isDistinct ? 'DISTINCT ' : ''); $sqlSelectExpressions = array_filter(array_map(array($this, 'walkSelectExpression'), $selectClause->selectExpressions)); if ($this->query->getHint(Query::HINT_INTERNAL_ITERATION) == true && $selectClause->isDistinct) { $this->query->setHint(self::HINT_DISTINCT, true); } $addMetaColumns = !$this->query->getHint(Query::HINT_FORCE_PARTIAL_LOAD) && $this->query->getHydrationMode() == Query::HYDRATE_OBJECT || $this->query->getHydrationMode() != Query::HYDRATE_OBJECT && $this->query->getHint(Query::HINT_INCLUDE_META_COLUMNS); foreach ($this->selectedClasses as $selectedClass) { $class = $selectedClass['class']; $dqlAlias = $selectedClass['dqlAlias']; $resultAlias = $selectedClass['resultAlias']; // Register as entity or joined entity result if ($this->queryComponents[$dqlAlias]['relation'] === null) { $this->rsm->addEntityResult($class->name, $dqlAlias, $resultAlias); } else { $this->rsm->addJoinedEntityResult($class->name, $dqlAlias, $this->queryComponents[$dqlAlias]['parent'], $this->queryComponents[$dqlAlias]['relation']['fieldName']); } if ($class->isInheritanceTypeSingleTable() || $class->isInheritanceTypeJoined()) { // Add discriminator columns to SQL $rootClass = $this->em->getClassMetadata($class->rootEntityName); $tblAlias = $this->getSQLTableAlias($rootClass->getTableName(), $dqlAlias); $discrColumn = $rootClass->discriminatorColumn; $columnAlias = $this->getSQLColumnAlias($discrColumn['name']); $sqlSelectExpressions[] = $tblAlias . '.' . $discrColumn['name'] . ' AS ' . $columnAlias; $this->rsm->setDiscriminatorColumn($dqlAlias, $columnAlias); $this->rsm->addMetaResult($dqlAlias, $columnAlias, $discrColumn['fieldName']); } // Add foreign key columns to SQL, if necessary if (!$addMetaColumns && !$class->containsForeignIdentifier) { continue; } // Add foreign key columns of class and also parent classes foreach ($class->associationMappings as $assoc) { if (!($assoc['isOwningSide'] && $assoc['type'] & ClassMetadata::TO_ONE)) { continue; } else { if (!$addMetaColumns && !isset($assoc['id'])) { continue; } } $owningClass = isset($assoc['inherited']) ? $this->em->getClassMetadata($assoc['inherited']) : $class; $sqlTableAlias = $this->getSQLTableAlias($owningClass->getTableName(), $dqlAlias); foreach ($assoc['targetToSourceKeyColumns'] as $srcColumn) { $columnAlias = $this->getSQLColumnAlias($srcColumn); $sqlSelectExpressions[] = $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias; $this->rsm->addMetaResult($dqlAlias, $columnAlias, $srcColumn, isset($assoc['id']) && $assoc['id'] === true); } } // Add foreign key columns to SQL, if necessary if (!$addMetaColumns) { continue; } // Add foreign key columns of subclasses foreach ($class->subClasses as $subClassName) { $subClass = $this->em->getClassMetadata($subClassName); $sqlTableAlias = $this->getSQLTableAlias($subClass->getTableName(), $dqlAlias); foreach ($subClass->associationMappings as $assoc) { // Skip if association is inherited if (isset($assoc['inherited'])) { continue; } if (!($assoc['isOwningSide'] && $assoc['type'] & ClassMetadata::TO_ONE)) { continue; } foreach ($assoc['targetToSourceKeyColumns'] as $srcColumn) { $columnAlias = $this->getSQLColumnAlias($srcColumn); $sqlSelectExpressions[] = $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias; $this->rsm->addMetaResult($dqlAlias, $columnAlias, $srcColumn); } } } } $sql .= implode(', ', $sqlSelectExpressions); return $sql; }
/** * [romanb: 2000 rows => 0.6 seconds] * * MAXIMUM TIME: 1 second */ public function testMixedQueryFetchJoinFullObjectHydrationPerformance2000Rows() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsPhonenumber', 'p', 'u', 'phonenumbers'); $rsm->addFieldResult('u', 'u__id', 'id'); $rsm->addFieldResult('u', 'u__status', 'status'); $rsm->addFieldResult('u', 'u__username', 'username'); $rsm->addFieldResult('u', 'u__name', 'name'); $rsm->addScalarResult('sclr0', 'nameUpper'); $rsm->addFieldResult('p', 'p__phonenumber', 'phonenumber'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsAddress', 'a', 'u', 'address'); $rsm->addFieldResult('a', 'a__id', 'id'); // Faked result set $resultSet = array(array('u__id' => '1', 'u__status' => 'developer', 'u__username' => 'romanb', 'u__name' => 'Roman', 'sclr0' => 'ROMANB', 'p__phonenumber' => '42', 'a__id' => '1')); for ($i = 2; $i < 2000; ++$i) { $resultSet[] = array('u__id' => $i, 'u__status' => 'developer', 'u__username' => 'jwage', 'u__name' => 'Jonathan', 'sclr0' => 'JWAGE' . $i, 'p__phonenumber' => '91', 'a__id' => $i); } $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\ObjectHydrator($this->_em); $this->setMaxRunningTime(1); $s = microtime(true); $result = $hydrator->hydrateAll($stmt, $rsm); $e = microtime(true); echo __FUNCTION__ . " - " . ($e - $s) . " seconds" . PHP_EOL; }
/** * @return object */ public function getBySearchQuery($searchQuery, $offset, $limit) { $rsm = new ResultSetMapping(); $rsm->addEntityResult('AppBundle\\Entity\\Post', 'p'); $rsm->addFieldResult('p', 'post_id', 'id'); $rsm->addFieldResult('p', 'post_title', 'title'); $rsm->addFieldResult('p', 'post_url', 'url'); $rsm->addFieldResult('p', 'post_tag', 'tag'); $rsm->addFieldResult('p', 'post_upvote_total', 'upvoteTotal'); $rsm->addFieldResult('p', 'post_created_at', 'createdAt'); $rsm->addJoinedEntityResult('AppBundle\\Entity\\User', 'u', 'p', 'user'); $rsm->addFieldResult('u', 'user_id', 'id'); $rsm->addFieldResult('u', 'user_username', 'username'); $rsm->addFieldResult('u', 'user_picture_path', 'picturePath'); $sql = sprintf("\n SELECT\n p.id AS post_id,\n p.title AS post_title,\n p.url AS post_url,\n p.tag AS post_tag,\n p.upvote_total AS post_upvote_total,\n p.created_at AS post_created_at,\n p.updated_at AS post_updated_at,\n u.id AS user_id,\n u.username AS user_username,\n u.picture_path AS user_picture_path\n FROM\n post AS p\n LEFT JOIN\n user AS u ON u.id = p.user_id\n WHERE\n p.title REGEXP '[[:<:]]%s[[:>:]]'\n ORDER BY\n p.upvote_total DESC\n LIMIT\n %d, %d\n ", $searchQuery, $offset, $limit); $query = $this->_em->createNativeQuery($sql, $rsm); return $query->getResult(); }
/** * @group DDC-1358 */ public function testMissingIdForSingleValuedChildEntity() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsAddress', 'a', 'u', 'address'); $rsm->addFieldResult('u', 'u__id', 'id'); $rsm->addFieldResult('u', 'u__status', 'status'); $rsm->addScalarResult('sclr0', 'nameUpper'); $rsm->addFieldResult('a', 'a__id', 'id'); $rsm->addFieldResult('a', 'a__city', 'city'); $rsm->addMetaResult('a', 'user_id', 'user_id'); // Faked result set $resultSet = array(array('u__id' => '1', 'u__status' => 'developer', 'sclr0' => 'ROMANB', 'a__id' => 1, 'a__city' => 'Berlin'), array('u__id' => '2', 'u__status' => 'developer', 'sclr0' => 'BENJAMIN', 'a__id' => null, 'a__city' => null)); $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\ObjectHydrator($this->_em); $result = $hydrator->hydrateAll($stmt, $rsm, array(Query::HINT_FORCE_PARTIAL_LOAD => true)); $this->assertEquals(2, count($result)); $this->assertInstanceOf('Doctrine\\Tests\\Models\\CMS\\CmsAddress', $result[0][0]->address); $this->assertNull($result[1][0]->address); }
/** * Gets the SQL fragment with the list of columns to select when querying for * an entity in this persister. * * Subclasses should override this method to alter or change the select column * list SQL fragment. Note that in the implementation of BasicEntityPersister * the resulting SQL fragment is generated only once and cached in {@link _selectColumnListSql}. * Subclasses may or may not do the same. * * @return string The SQL fragment. * @todo Rename: _getSelectColumnsSQL() */ protected function _getSelectColumnListSQL() { if ($this->_selectColumnListSql !== null) { return $this->_selectColumnListSql; } $columnList = ''; $this->_rsm = new Query\ResultSetMapping(); $this->_rsm->addEntityResult($this->_class->name, 'r'); // r for root // Add regular columns to select list foreach ($this->_class->fieldNames as $field) { if ($columnList) { $columnList .= ', '; } $columnList .= $this->_getSelectColumnSQL($field, $this->_class); } $this->_selectJoinSql = ''; $eagerAliasCounter = 0; foreach ($this->_class->associationMappings as $assocField => $assoc) { $assocColumnSQL = $this->_getSelectColumnAssociationSQL($assocField, $assoc, $this->_class); if ($assocColumnSQL) { if ($columnList) { $columnList .= ', '; } $columnList .= $assocColumnSQL; } if ($assoc['type'] & ClassMetadata::TO_ONE && ($assoc['fetch'] == ClassMetadata::FETCH_EAGER || !$assoc['isOwningSide'])) { $eagerEntity = $this->_em->getClassMetadata($assoc['targetEntity']); if ($eagerEntity->inheritanceType != ClassMetadata::INHERITANCE_TYPE_NONE) { continue; // now this is why you shouldn't use inheritance } $assocAlias = 'e' . $eagerAliasCounter++; $this->_rsm->addJoinedEntityResult($assoc['targetEntity'], $assocAlias, 'r', $assocField); foreach ($eagerEntity->fieldNames as $field) { if ($columnList) { $columnList .= ', '; } $columnList .= $this->_getSelectColumnSQL($field, $eagerEntity, $assocAlias); } foreach ($eagerEntity->associationMappings as $assoc2Field => $assoc2) { $assoc2ColumnSQL = $this->_getSelectColumnAssociationSQL($assoc2Field, $assoc2, $eagerEntity, $assocAlias); if ($assoc2ColumnSQL) { if ($columnList) { $columnList .= ', '; } $columnList .= $assoc2ColumnSQL; } } $this->_selectJoinSql .= ' LEFT JOIN'; // TODO: Inner join when all join columns are NOT nullable. $first = true; if ($assoc['isOwningSide']) { $this->_selectJoinSql .= ' ' . $eagerEntity->table['name'] . ' ' . $this->_getSQLTableAlias($eagerEntity->name, $assocAlias) . ' ON '; foreach ($assoc['sourceToTargetKeyColumns'] as $sourceCol => $targetCol) { if (!$first) { $this->_selectJoinSql .= ' AND '; } $this->_selectJoinSql .= $this->_getSQLTableAlias($assoc['sourceEntity']) . '.' . $sourceCol . ' = ' . $this->_getSQLTableAlias($assoc['targetEntity'], $assocAlias) . '.' . $targetCol . ' '; $first = false; } } else { $eagerEntity = $this->_em->getClassMetadata($assoc['targetEntity']); $owningAssoc = $eagerEntity->getAssociationMapping($assoc['mappedBy']); $this->_selectJoinSql .= ' ' . $eagerEntity->table['name'] . ' ' . $this->_getSQLTableAlias($eagerEntity->name, $assocAlias) . ' ON '; foreach ($owningAssoc['sourceToTargetKeyColumns'] as $sourceCol => $targetCol) { if (!$first) { $this->_selectJoinSql .= ' AND '; } $this->_selectJoinSql .= $this->_getSQLTableAlias($owningAssoc['sourceEntity'], $assocAlias) . '.' . $sourceCol . ' = ' . $this->_getSQLTableAlias($owningAssoc['targetEntity']) . '.' . $targetCol . ' '; $first = false; } } } } $this->_selectColumnListSql = $columnList; return $this->_selectColumnListSql; }
/** * @group DDC-1470 * * @expectedException \Doctrine\ORM\Internal\Hydration\HydrationException * @expectedExceptionMessage The discriminator column "discr" is missing for "Doctrine\Tests\Models\Company\CompanyEmployee" using the DQL alias "e". */ public function testMissingDiscriminatorColumnException() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\Company\\CompanyFixContract', 'c'); $rsm->addJoinedEntityResult('Doctrine\\Tests\\Models\\Company\\CompanyEmployee', 'e', 'c', 'salesPerson'); $rsm->addFieldResult('c', 'c__id', 'id'); $rsm->addMetaResult('c', 'c_discr', 'discr'); $rsm->setDiscriminatorColumn('c', 'c_discr'); $rsm->addFieldResult('e', 'e__id', 'id'); $rsm->addFieldResult('e', 'e__name', 'name'); $rsm->addMetaResult('e ', 'e_discr', 'discr'); $rsm->setDiscriminatorColumn('e', 'e_discr'); $resultSet = array(array('c__id' => '1', 'c_discr' => 'fix', 'e__id' => '1', 'e__name' => 'Fabio B. Silva')); $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\ObjectHydrator($this->_em); $hydrator->hydrateAll($stmt, $rsm); }