PHP ResultSetMapping::addEntityResult方法代码示例

 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');
 }

 /**
  * 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']);
 }

 /**
  * 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();
 }

 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;
 }

 /**
  * 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;
 }