/** * 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>'); }
/** * Select u.id, u.name from CmsUser u */ public function testNewHydrationSimpleEntityQuery() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Kitpages\\DataGridBundle\\Tests\\TestEntities\\Node', 'node'); $rsm->addEntityResult('Kitpages\\DataGridBundle\\Tests\\TestEntities\\NodeAssoc', 'assoc'); $rsm->addFieldResult('node', 'id1', 'id'); $rsm->addFieldResult('node', 'user2', 'user'); $rsm->addFieldResult('node', 'content3', 'content'); $rsm->addFieldResult('node', 'parent_id4', 'parentId'); $rsm->addFieldResult('node', 'created_at5', 'createdAt'); $rsm->addFieldResult('assoc', 'id6', 'id'); $rsm->addFieldResult('assoc', 'name7', 'name'); $rsm->addScalarResult('intervals8', 'intervals'); // Faked result set $resultSet = array(array('id1' => 11, 'user2' => 'toto', 'content3' => 'I like it!', 'parent_id4' => 0, 'created_at5' => new \DateTime('2010-04-21 12:14:20'), 'id6' => 1, 'name7' => 'tutu', 'intervals8' => 10)); $stmt = new HydratorMockStatement($resultSet); $hydrator = new DataGridHydrator($this->getEntityManager()); $result = $hydrator->hydrateAll($stmt, $rsm); $this->assertTrue(is_array($result)); $this->assertEquals(1, count($result)); $this->assertEquals(11, $result[0]['node.id']); $this->assertEquals('toto', $result[0]['node.user']); $this->assertEquals('I like it!', $result[0]['node.content']); $this->assertEquals(0, $result[0]['node.parentId']); $this->assertEquals(new \DateTime('2010-04-21 12:14:20'), $result[0]['node.createdAt']); $this->assertEquals(1, $result[0]['assoc.id']); $this->assertEquals('tutu', $result[0]['assoc.name']); $this->assertEquals(10, $result[0]['intervals']); }
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(); }
public function getNotasPorPeriodo($periodo, $alumno) { $planestuRepository = $this->getDoctrine()->getRepository('AppBundle:Planestu'); $em = $this->getDoctrine()->getManager(); // Configuracion de la entidad a adoptar la consulta $rsm = new ResultSetMapping(); $rsm->addEntityResult('AppBundle:Materias', 'u'); $rsm->addFieldResult('u', 'cmate', 'cmate'); $rsm->addFieldResult('u', 'nmate', 'nmate'); // plan de estudio exceptuando las materias excluidas $query = $em->createNativeQuery('SELECT * FROM materias LEFT JOIN planestu ON materias.cmate = planestu.cmate WHERE planestu.cgrupo=? AND materias.cmate NOT IN (SELECT mateexclu.cmate FROM mateexclu WHERE calum =? AND (cperi =? OR cperi =?)) ORDER BY orden', $rsm); // set parametros al query $query->setParameter(1, $alumno->getCgrupo()->getCgrupo()); $query->setParameter(2, $alumno->getCalum()); $query->setParameter(3, $periodo); $query->setParameter(4, 'T'); $materias = $query->getResult(); $notasArray = array(); foreach ($materias as $materia) { $nota = $this->getNota($materia->getCmate(), $alumno->getCalum(), $periodo); $notasArray[$materia->getNmate()] = $nota; } return $notasArray; }
public function getStatistics() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('AppBundle:Stats', 's'); $rsm->addFieldResult('s', 'player', 'player'); $rsm->addFieldResult('s', 'win', 'win'); $rsm->addFieldResult('s', 'lose', 'lose'); $rsm->addFieldResult('s', 'draw', 'draw'); $rsm->addFieldResult('s', 'rocks', 'rocks'); $rsm->addFieldResult('s', 'papers', 'papers'); $rsm->addFieldResult('s', 'scissors', 'scissors'); $rsm->addFieldResult('s', 'lizards', 'lizards'); $rsm->addFieldResult('s', 'spocks', 'spocks'); $query = $this->_em->createNativeQuery('SELECT (SELECT name FROM player WHERE id = player_id) as player, SUM(CASE WHEN result = 1 THEN 1 ELSE 0 END ) as win, SUM(CASE WHEN result = 0 THEN 1 ELSE 0 END ) as lose, SUM(CASE WHEN result = 2 THEN 1 ELSE 0 END ) as draw, SUM(CASE WHEN threw = \'Rock\' THEN 1 ELSE 0 END ) as rocks, SUM(CASE WHEN threw = \'Paper\' THEN 1 ELSE 0 END ) as papers, SUM(CASE WHEN threw = \'Scissors\' THEN 1 ELSE 0 END ) as scissors, SUM(CASE WHEN threw = \'Lizard\' THEN 1 ELSE 0 END ) as lizards, SUM(CASE WHEN threw = \'Spock\' THEN 1 ELSE 0 END ) as spocks FROM stat GROUP BY player_id ORDER BY player_id', $rsm); return $query->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; }
/** * select u.name from CmsUser u where u.id = 1 * * @dataProvider singleScalarResultSetProvider */ public function testHydrateSingleScalar($name, $resultSet) { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $rsm->addFieldResult('u', 'u__id', 'id'); $rsm->addFieldResult('u', 'u__name', 'name'); $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\SingleScalarHydrator($this->_em); if ($name == 'result1') { $result = $hydrator->hydrateAll($stmt, $rsm); $this->assertEquals('romanb', $result); } else { if ($name == 'result2') { $result = $hydrator->hydrateAll($stmt, $rsm); $this->assertEquals(1, $result); } else { if ($name == 'result3' || $name == 'result4') { try { $result = $hydrator->hydrateAll($stmt, $rsm); $this->fail(); } catch (\Doctrine\ORM\NonUniqueResultException $e) { } } } } }
/** * @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 getUnassignedUser() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('AppBundle:User', 'u'); $rsm->addFieldResult('u', 'id', 'id'); $rsm->addFieldResult('u', 'username', 'username'); $rsm->addFieldResult('u', 'email', 'email'); $q = $this->getEntityManager()->createNativeQuery('SELECT u.id, u.username, u.email FROM `user` as u WHERE u.id IN (SELECT getUnassginedUser())', $rsm); return $q->getResult(); }
private function _getCommonRSM() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('UserBundle:AdminUserRole', 'a'); $rsm->addFieldResult('a', 'id', 'id'); $rsm->addFieldResult('a', 'name', 'name'); $rsm->addFieldResult('a', 'label', 'label'); $rsm->addFieldResult('a', 'status', 'status'); return $rsm; }
public function getChiefsByOrchestra($codeOrchestra) { $rsm = new ResultSetMapping(); $rsm->addEntityResult('IUT\\CatalogBundle\\Entity\\Musicien', 'c'); $rsm->addFieldResult('c', 'Code_Musicien', 'codeMusicien'); $rsm->addFieldResult('c', 'Nom_Musicien', 'nomMusicien'); $rsm->addFieldResult('c', 'Prénom_Musicien', 'prénomMusicien'); $rsm->addFieldResult('c', 'Année_Naissance', 'annéeNaissance'); $rsm->addFieldResult('c', 'Photo', 'Image'); return $this->getEntityManager()->createNativeQuery('SELECT DISTINCT c.* FROM Musicien c ' . 'inner join Direction ON c.Code_Musicien = Direction.Code_Musicien ' . 'inner join Orchestres on Direction.Code_Orchestre = Orchestres.Code_Orchestre ' . 'where Orchestres.Code_Orchestre = ? ', $rsm)->setParameter(1, $codeOrchestra)->getResult(); }
public function hasPasswordExpired($userid, $em) { $settings = $em->getRepository("\\Application\\Entity\\Settings")->find(1); $rsm = new ResultSetMapping(); $rsm->addEntityResult('Application\\Entity\\User', 'u'); $rsm->addFieldResult('u', 'PASSWORDLASTCHANGED', 'passwordlastchanged'); $query = $em->createNativeQuery("SELECT PASSWORDLASTCHANGED FROM user " . " where DATEDIFF(CURDATE(),PASSWORDLASTCHANGED) > :num1 AND PK_USERID = :num2 ", $rsm); $query->setParameter('num1', $settings->getPasswordExpireydays()); $query->setParameter('num2', $userid); return count($query->getResult()) > 0 ? true : false; }
/** * @return ResultSetMapping */ public function getResultMapping() { $rsm = new ResultSetMapping(); $rsm->addEntityResult(DaemonTask::class, 'dt'); $rsm->addFieldResult('dt', 'id', 'id'); $rsm->addFieldResult('dt', 'due_at', 'dueAt'); $rsm->addFieldResult('dt', 'handler_class', 'handlerClass'); $rsm->addFieldResult('dt', 'arguments', 'arguments'); $rsm->addFieldResult('dt', 'disabled', 'disabled'); $rsm->addFieldResult('dt', 'reoccur_interval', 'reoccurInterval'); return $rsm; }
protected function execute(InputInterface $input, OutputInterface $output) { /** @var ObjectManager $em */ $em = $this->getContainer()->get('doctrine')->getManager(); $currentUtc = (new \DateTime())->setTimezone(new \DateTimeZone('UTC')); $calculationDate = clone $currentUtc; $calculationDate->modify('midnight'); $systemOffset = (new \DateTime())->getOffset(); $rsm = new ResultSetMapping(); $rsm->addEntityResult('AcmeUserBundle:User', 'u'); $rsm->addFieldResult('u', 'id', 'id'); $rsm->addFieldResult('u', 'timezone', 'timezone'); $str = 'select u.id as id, u.timezone from ed_users u WHERE DATE_ADD("' . $currentUtc->format('Y-m-d H:i') . '", INTERVAL u.timezone HOUR) > "' . $calculationDate->format('Y-m-d H:i') . '" AND NOT EXISTS(SELECT * FROM ed_progress_statistics ps WHERE ps.user_id=u.id AND DATE_ADD(ps.calculated_at, INTERVAL (u.timezone - ' . $systemOffset / 3600 . ') HOUR) > "' . $calculationDate->format('Y-m-d H:i') . '") '; $query = $em->createNativeQuery($str, $rsm); $users = $query->getResult(); $dayStart = clone $calculationDate; $dayStart->sub(new \DateInterval('P1D')); $dayFinish = clone $calculationDate; foreach ($users as $user) { // $user = $em->find('AcmeUserBundle:User', $user->getId()); $progress = new ProgressStatistic(); $progress->setUser($user); $queryBuilder = $em->createQueryBuilder(); $actions = $queryBuilder->from('AcmeEdelaBundle:UserAction', 'ua')->addSelect('uap.result as progress')->addSelect('IDENTITY(a.goal) as goal_id')->leftJoin('AcmeEdelaBundle:Action', 'a', Join::WITH, 'ua.action=a')->leftJoin('AcmeUserBundle:User', 'u', Join::WITH, 'ua.user=u')->leftJoin('AcmeEdelaBundle:UserActionProgress', 'uap', Join::WITH, 'uap.userAction=ua AND DATE_ADD2(uap.createdAt, INTERVAL (u.timezone - ' . $systemOffset / 3600 . ') HOUR) > :dayStart AND DATE_ADD2(uap.createdAt, INTERVAL (u.timezone - ' . $systemOffset / 3600 . ') HOUR) < :dayFinish ')->setParameter('dayFinish', $dayFinish)->setParameter('dayStart', $dayStart)->where('ua.user = :user')->setParameter('user', $user); $orX = $queryBuilder->expr()->orX(); $orX->add($queryBuilder->expr()->gt('BIT_AND(ua.periodicity, :dayOfWeek)', '0')); $orX->add($queryBuilder->expr()->eq('MOD(DATE_DIFF(ua.createdAt, :date), ua.periodicityInterval)', '0')); $actions->andWhere($orX)->setParameter('date', $dayStart)->setParameter('dayOfWeek', (int) (1 << $dayStart->format('w'))); $actions->andWhere('ua.startAt < :dayFinish'); var_dump($actions->getQuery()->getSQL(), $dayFinish, $dayStart); $actions = $actions->getQuery()->getArrayResult(); var_dump($actions); $progress->setTotalActions(count($actions)); $progress->setProgressedActions(count(array_filter($actions, function ($item) { return (bool) $item['progress']; }))); $goaledActions = count(array_filter($actions, function ($item) { return (bool) $item['goal_id']; })); $progress->setEfficiency($progress->getProgressedActions() * 3 + $goaledActions); $em->persist($progress); } $em->flush(); $output->writeln('Done'); }
/** * Returns all competences linked to the specific matiere * * @param $matiereID */ public function findByExamenId($examenID) { $sql = "SELECT c.id, c.name " . "FROM c3csi_examen exam " . "LEFT JOIN c3csi_examen_rel_competence exam_comp ON exam_comp.examen_id = exam.id " . "LEFT JOIN c3csi_competence c ON c.id = exam_comp.competence_id " . "WHERE exam.id = ?"; $rsm = new ResultSetMapping(); $rsm->addEntityResult('SkillBundle\\Entity\\Competence', 'c'); $rsm->addFieldResult('c', 'id', 'id'); $rsm->addFieldResult('c', 'name', 'name'); $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameter(1, $examenID); $result = $query->getResult(); return $result; }
/** * @param Entity\Contact $contact * @param Entity\Facebook $facebook * @return bool */ public function isContactInFacebook(Entity\Contact $contact, Entity\Facebook $facebook) { $resultSetMap = new ResultSetMapping(); $resultSetMap->addEntityResult('Contact\\Entity\\Contact', 'c'); /** * Don't map the contact_id because that will overwrite the existing contact object leaving an emtpy one */ $resultSetMap->addFieldResult('c', 'email', 'email'); $queryInString = sprintf("SELECT %s FROM %s WHERE %s", $facebook->getContactKey(), $facebook->getFromClause(), $facebook->getWhereClause()); $query = $this->getEntityManager()->createNativeQuery(sprintf("SELECT email FROM contact WHERE contact_id = %s AND contact_id IN (%s) AND date_end IS NULL", $contact->getId(), $queryInString), $resultSetMap); return sizeof($query->getResult()) > 0; }
public function testHydrateColumn() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $rsm->addFieldResult('u', 'u__name', 'name'); // Faked result set $resultSet = array(array('u__name' => 'romanb'), array('u__name' => 'jwage')); $stmt = new HydratorMockStatement($resultSet); $hydrator = new ColumnHydrator($this->_em); $result = $hydrator->hydrateAll($stmt, $rsm); $this->assertEquals(array('romanb', 'jwage'), $result); }
/** * Returns the examens related to the intervenant passed in parameter * * @param $intervenantID */ public function findByIntervenantId($intervenantID) { $sql = "SELECT exam.id, exam.name, exam.description " . "FROM c3csi_user user " . "LEFT JOIN c3csi_examen exam ON exam.intervenant_id = user.id " . "WHERE user.id = ?"; $rsm = new ResultSetMapping(); $rsm->addEntityResult('EvaluationBundle\\Entity\\Examen', 'm'); $rsm->addFieldResult('m', 'id', 'id'); $rsm->addFieldResult('m', 'name', 'name'); $rsm->addFieldResult('m', 'description', 'description'); $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameter(1, $intervenantID); $result = $query->getResult(); return $result; }
/** * @group DDC-1470 * * @expectedException \Doctrine\ORM\Internal\Hydration\HydrationException * @expectedExceptionMessage The discriminator column "discr" is missing for "Doctrine\Tests\Models\Company\CompanyPerson" using the DQL alias "p". */ public function testMissingDiscriminatorColumnException() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\Company\\CompanyPerson', 'p'); $rsm->addFieldResult('p', 'p__id', 'id'); $rsm->addFieldResult('p', 'p__name', 'name'); $rsm->addMetaResult('p ', 'discr', 'discr'); $rsm->setDiscriminatorColumn('p', 'discr'); $resultSet = array(array('u__id' => '1', 'u__name' => 'Fabio B. Silva')); $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\SimpleObjectHydrator($this->_em); $hydrator->hydrateAll($stmt, $rsm); }
public function getOeuvresByMusician($codeMusicien) { $rsm = new ResultSetMapping(); $rsm->addEntityResult('IUT\\CatalogBundle\\Entity\\Oeuvre', 'a'); $rsm->addFieldResult('a', 'Code_Oeuvre', 'codeOeuvre'); $rsm->addFieldResult('a', 'Titre_Oeuvre', 'titreOeuvre'); $rsm->addFieldResult('a', 'Sous_Titre', 'sousTitre'); $rsm->addFieldResult('a', 'Tonalité', 'tonalité'); $rsm->addFieldResult('a', 'Année', 'année'); $rsm->addFieldResult('a', 'Opus', 'opus'); $rsm->addFieldResult('a', 'Numéro_Opus', 'numéroOpus'); $rsm->addFieldResult('a', 'Code_Type', 'codeType'); return $this->getEntityManager()->createNativeQuery('SELECT DISTINCT a.* FROM Oeuvre a ' . 'inner join Composer ON a.Code_Oeuvre = Composer.Code_Oeuvre ' . 'inner join Musicien on Composer.Code_Musicien = Musicien.Code_Musicien ' . 'where Musicien.Code_Musicien = ? ', $rsm)->setParameter(1, $codeMusicien)->getResult(); }
public function getAlbumsByOrchestra($codeOrchestra) { $rsm = new ResultSetMapping(); $rsm->addEntityResult('IUT\\CatalogBundle\\Entity\\Album', 'a'); $rsm->addFieldResult('a', 'Code_Album', 'codeAlbum'); $rsm->addFieldResult('a', 'Titre_Album', 'titreAlbum'); $rsm->addFieldResult('a', 'Année_Album', 'anneeAlbum'); //$rsm->addFieldResult('a', 'Code_Genre', 'codeGenre'); // $rsm->addFieldResult('a', 'Code_Editeur', 'codeEditeur'); $rsm->addFieldResult('a', 'Pochette', 'pochette'); $rsm->addFieldResult('a', 'ASIN', 'ASIN'); // build rsm here return $this->getEntityManager()->createNativeQuery('SELECT DISTINCT a.* FROM Album a ' . 'inner join Disque ON a.Code_Album = Disque.Code_Album ' . 'inner join Composition_Disque on Disque.Code_Disque = Composition_Disque.Code_Disque ' . 'inner join Enregistrement on Composition_Disque.Code_Morceau = Enregistrement.Code_Morceau ' . 'inner join Direction on Enregistrement.Code_Morceau = Direction.Code_Morceau ' . 'inner join Orchestres on Direction.Code_Orchestre = Orchestres.Code_Orchestre ' . 'where Orchestres.Code_Orchestre = ? ', $rsm)->setParameter(1, $codeOrchestra)->getResult(); }
/** * @group DDC-644 */ public function testSkipUnknownColumns() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $rsm->addFieldResult('u', 'u__id', 'id'); $rsm->addFieldResult('u', 'u__name', 'name'); $rsm->addScalarResult('foo1', 'foo'); $rsm->addScalarResult('bar2', 'bar'); $rsm->addScalarResult('baz3', 'baz'); $resultSet = array(array('u__id' => '1', 'u__name' => 'romanb', 'foo1' => 'A', 'bar2' => 'B', 'baz3' => 'C', 'foo' => 'bar')); $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\ScalarHydrator($this->_em); $result = $hydrator->hydrateAll($stmt, $rsm); }
public function findTopScoresForEntity($targetClassName, $limit, $reversed) { $rsm = new ResultSetMapping(); $rsm->addEntityResult($targetClassName, 't'); $rsm->addFieldResult('score', 'vote', 'score'); $sql = sprintf('SELECT SUM(v.vote) AS score FROM votes v INNER JOIN %s t ON v.target_id = t.id GROUP BY v.target_id HAVING score %s 0 ORDER BY score %s LIMIT %d', $targetClassName, $reversed ? '<' : '>', $reversed ? 'ASC' : 'DESC', $limit); $query = $this->_em->createNativeQuery($sql, $rsm); return $query->getResult(); }
/** * For SQL: SELECT id, status, username, name FROM cms_users */ public function testBasicResultSetMapping() { $this->_rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsUser', 'u'); $this->_rsm->addFieldResult('u', 'id', 'id'); $this->_rsm->addFieldResult('u', 'status', 'status'); $this->_rsm->addFieldResult('u', 'username', 'username'); $this->_rsm->addFieldResult('u', 'name', 'name'); $this->assertFalse($this->_rsm->isScalarResult('id')); $this->assertFalse($this->_rsm->isScalarResult('status')); $this->assertFalse($this->_rsm->isScalarResult('username')); $this->assertFalse($this->_rsm->isScalarResult('name')); $this->assertTrue($this->_rsm->getClassName('u') == 'Doctrine\\Tests\\Models\\CMS\\CmsUser'); $class = $this->_rsm->getDeclaringClass('id'); $this->assertTrue($class == 'Doctrine\\Tests\\Models\\CMS\\CmsUser'); $this->assertEquals('u', $this->_rsm->getEntityAlias('id')); $this->assertEquals('u', $this->_rsm->getEntityAlias('status')); $this->assertEquals('u', $this->_rsm->getEntityAlias('username')); $this->assertEquals('u', $this->_rsm->getEntityAlias('name')); $this->assertEquals('id', $this->_rsm->getFieldName('id')); $this->assertEquals('status', $this->_rsm->getFieldName('status')); $this->assertEquals('username', $this->_rsm->getFieldName('username')); $this->assertEquals('name', $this->_rsm->getFieldName('name')); }
/** * Retrieves BranchEmailConfiguration using $supportAddress and $customerDomain as Criteria * * @param $supportAddress * @param $customerDomain * @return int|null * @throws \Doctrine\ORM\NonUniqueResultException */ public function getBySupportAddressAndCustomerDomainCriteria($supportAddress, $customerDomain) { $customerDomainRegExp = "[[:<:]]" . $customerDomain . "[[:>:]]"; $rsm = new ResultSetMapping(); $rsm->addEntityResult('DiamanteDeskBundle:BranchEmailConfiguration', 'j'); $rsm->addScalarResult('branch_id', 'branch_id'); $rsm->addScalarResult('criteria', 'criteria'); $result = $this->getEntityManager()->createNativeQuery("\n SELECT j.branch_id as branch_id,\n (j.support_address = :supportAddress AND\n j.customer_domains REGEXP :customerDomainRegExp) * 3 +\n (j.support_address = :supportAddress AND\n j.customer_domains = '') * 2 +\n (j.support_address = '' AND\n j.customer_domains REGEXP :customerDomainRegExp) * 1\n AS criteria\n FROM diamante_branch_email_configuration j ORDER BY criteria DESC LIMIT 1\n ", $rsm)->setParameter('supportAddress', $supportAddress)->setParameter('customerDomainRegExp', $customerDomainRegExp)->getOneOrNullResult(); if ($result['criteria']) { return $result['branch_id']; } else { return null; } }
/** * @param $id */ public function findOneById($id, $fullObject = true) { $sql = "SELECT *" . "FROM c3csi_matiere m WHERE id = ?"; $rsm = new ResultSetMapping(); $rsm->addEntityResult('SchoolBundle\\Entity\\Matiere', 'm'); $rsm->addFieldResult('m', 'id', 'id'); $rsm->addFieldResult('m', 'name', 'name'); $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameter(1, $id); $result = $query->getResult(); if (isset($result[0])) { return $result[0]; } return $result; }
public function testExtraFieldInResultSetShouldBeIgnore() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('Doctrine\\Tests\\Models\\CMS\\CmsAddress', 'a'); $rsm->addFieldResult('a', 'a__id', 'id'); $rsm->addFieldResult('a', 'a__city', 'city'); $resultSet = array(array('a__id' => '1', 'a__city' => 'Cracow', 'doctrine_rownum' => '1')); $expectedEntity = new \Doctrine\Tests\Models\CMS\CmsAddress(); $expectedEntity->id = 1; $expectedEntity->city = 'Cracow'; $stmt = new HydratorMockStatement($resultSet); $hydrator = new \Doctrine\ORM\Internal\Hydration\SimpleObjectHydrator($this->_em); $result = $hydrator->hydrateAll($stmt, $rsm); $this->assertEquals($result[0], $expectedEntity); }
/** * @return Event[] */ public function findByCurrentEvents() { $rsm = new ResultSetMapping(); $rsm->addEntityResult('BauerIncidentDashboardCoreBundle:Event', 'event'); $rsm->addFieldResult('event', 'id', 'id'); $rsm->addFieldResult('event', 'message', 'message'); $rsm->addFieldResult('event', 'identifier', 'identifier'); $rsm->addFieldResult('event', 'system', 'system'); $rsm->addFieldResult('event', 'status', 'status'); $rsm->addFieldResult('event', 'created', 'created'); $rsm->addFieldResult('event', 'url', 'url'); $rsm->addFieldResult('event', 'isUnique', 'unique'); $rsm->addFieldResult('event', 'type', 'type'); $query = $this->getEntityManager()->createNativeQuery('SELECT sub.* FROM (SELECT * FROM `event` ORDER BY `id` DESC) as sub GROUP BY `status`, `identifier` ORDER BY `created` DESC', $rsm); return $query->getResult(); }
/** * Returns a specific examen * * @param $id */ public function findOneById($id, $fullObject = true) { $sql = "SELECT * " . "FROM c3csi_type_note tn WHERE id = ?"; $rsm = new ResultSetMapping(); $rsm->addEntityResult('EvaluationBundle\\Entity\\TypeNote', 'tn'); $rsm->addFieldResult('tn', 'id', 'id'); $rsm->addFieldResult('tn', 'label', 'label'); $rsm->addFieldResult('tn', 'value', 'value'); $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameter(1, $id); $result = $query->getResult(); if (isset($result[0])) { return $result[0]; } return $result; }
/** * Get all ancillary services by institution medical center * * @param Mixed <InstitutionMedicalCenter, int> $institutionMedicalCenter * @return array OfferedService */ public function getAllServicesByInstitutionMedicalCenter($institutionMedicalCenter, $hydrationMode = Query::HYDRATE_OBJECT) { if ($institutionMedicalCenter instanceof InstitutionMedicalCenter) { $institutionMedicalCenterId = $institutionMedicalCenter->getId(); } else { $institutionMedicalCenterId = $institutionMedicalCenter; } $rsm = new ResultSetMapping(); $rsm->addEntityResult('AdminBundle:OfferedService', 'b'); $rsm->addFieldResult('b', 'id', 'id'); $rsm->addFieldResult('b', 'name', 'name'); $rsm->addFieldResult('b', 'status', 'status'); $rsm->addFieldResult('b', 'date_created', 'dateCreated'); $sql = "SELECT b.* FROM institution_medical_center_properties a JOIN offered_services b ON b.id = a.value \n WHERE a.institution_medical_center_id = :imcId\n AND a.institution_property_type_id = :propertyType\n AND b.status = 1\n ORDER BY b.name ASC"; $query = $this->getEntityManager()->createNativeQuery($sql, $rsm)->setParameter('imcId', $institutionMedicalCenterId)->setParameter('propertyType', InstitutionPropertyType::ANCILLIARY_SERVICE_ID); return $query->getResult($hydrationMode); }