PHP Query\ResultSetMapping类代码示例

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

 public function getCompanyStats(int $companyId) : CompanyStatsDTO
 {
     $resultSetMapping = new ResultSetMapping();
     $resultSetMapping->addScalarResult('sclr_0', 'totalActiveEmployees', 'integer')->addScalarResult('sclr_1', 'totalInactiveEmployees', 'integer');
     $companyStatsArray = $this->getEntityManager()->createQueryBuilder()->addSelect('SUM(IF(Employee.isActive=1,1,0)) AS totalActiveEmployees')->addSelect('SUM(IF(Employee.isActive=0,1,0)) AS totalInactiveEmployees')->from(Employee::class, 'Employee')->where('Employee.company = :companyId')->setParameter('companyId', $companyId)->setMaxResults(1)->getQuery()->setResultSetMapping($resultSetMapping)->getArrayResult();
     return new CompanyStatsDTO($companyStatsArray[0]['totalActiveEmployees'], $companyStatsArray[0]['totalInactiveEmployees']);
 }

 /**
  * @param Collection   $collection
  * @param array|string $catalogNumbers
  * @return array
  * @throws \Doctrine\ORM\NonUniqueResultException
  */
 public function findBestTaxonsByCatalogNumbers(Collection $collection, $catalogNumbers)
 {
     if (!is_array($catalogNumbers)) {
         $catalogNumbers = [$catalogNumbers];
     }
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('scientificname', 'scientificname');
     $rsm->addScalarResult('scientificnameauthorship', 'scientificnameauthorship');
     $rsm->addScalarResult('catalognumber', 'catalognumber');
     $nativeSqlTaxon = '
     WITH FirstIDentified AS (
        SELECT First_Value(t.taxonid) OVER (PARTITION BY catalognumber ORDER BY identificationverifstatus) First,
        t.taxonid, t.scientificname, t.scientificnameauthorship,
        s.catalognumber
        FROM Taxons t
        JOIN Determinations d ON t.taxonid = d.taxonid
        JOIN Specimens s on d.occurrenceid = s.occurrenceid
        WHERE s.collectioncode = :collectionCode AND
        s.catalognumber IN (:catalogNumbers)
     )
     SELECT catalognumber, scientificname, scientificnameauthorship FROM FirstIdentified
     WHERE taxonid = First
     ';
     $this->getEntityManager()->getConnection()->setFetchMode(\PDO::FETCH_ASSOC);
     $results = $this->getEntityManager()->getConnection()->executeQuery($nativeSqlTaxon, ['collectionCode' => $collection->getCollectioncode(), 'catalogNumbers' => $catalogNumbers], ['catalogNumbers' => Connection::PARAM_STR_ARRAY])->fetchAll();
     $formattedResult = [];
     if (count($results)) {
         foreach ($results as $values) {
             $formattedResult[$values['CATALOGNUMBER']] = Taxon::toString($values['SCIENTIFICNAME'], $values['SCIENTIFICNAMEAUTHORSHIP']);
         }
     }
     return $formattedResult;
 }

 public function countRecord()
 {
     $resultMapping = new ResultSetMapping();
     $resultMapping->addScalarResult('total', 'total');
     $result = $this->execute('SELECT SUM(ID) AS total FROM ' . $this->getTable(), array(), $resultMapping);
     return $result['total'];
 }

 /**
  * @param array $associationMapping
  * @param array $entityIds
  * @param array $config
  *
  * @return array [['entityId' => mixed, 'relatedEntityId' => mixed], ...]
  */
 public function getRelatedItemsIds($associationMapping, $entityIds, $config)
 {
     $limit = isset($config[ConfigUtil::MAX_RESULTS]) ? $config[ConfigUtil::MAX_RESULTS] : -1;
     if ($limit > 0 && count($entityIds) > 1) {
         $selectStmt = null;
         $subQueries = [];
         foreach ($entityIds as $id) {
             $subQuery = $this->getRelatedItemsIdsQuery($associationMapping, [$id], $config);
             $subQuery->setMaxResults($limit);
             // We should wrap all subqueries with brackets for PostgreSQL queries with UNION and LIMIT
             $subQueries[] = '(' . QueryUtils::getExecutableSql($subQuery) . ')';
             if (null === $selectStmt) {
                 $mapping = QueryUtils::parseQuery($subQuery)->getResultSetMapping();
                 $selectStmt = sprintf('entity.%s AS entityId, entity.%s AS relatedEntityId', QueryUtils::getColumnNameByAlias($mapping, 'entityId'), QueryUtils::getColumnNameByAlias($mapping, 'relatedEntityId'));
             }
         }
         $rsm = new ResultSetMapping();
         $rsm->addScalarResult('entityId', 'entityId')->addScalarResult('relatedEntityId', 'relatedEntityId');
         $qb = new SqlQueryBuilder($this->doctrineHelper->getEntityManager($associationMapping['targetEntity']), $rsm);
         $qb->select($selectStmt)->from('(' . implode(' UNION ALL ', $subQueries) . ')', 'entity');
         $rows = $qb->getQuery()->getScalarResult();
     } else {
         $query = $this->getRelatedItemsIdsQuery($associationMapping, $entityIds, $config);
         if ($limit >= 0) {
             $query->setMaxResults($limit);
         }
         $rows = $query->getScalarResult();
     }
     return $rows;
 }

 /**
  *
  * Relatório de Configurações das Classes WMI Dinâmico Detalhes
  *
  */
 public function relatorioWmiDinamico($property, $dataInicio, $dataFim)
 {
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('nm_rede', 'nm_rede');
     $rsm->addScalarResult('dt_hr_ult_acesso', 'dt_hr_ult_acesso');
     $rsm->addScalarResult('id_computador', 'id_computador');
     $rsm->addScalarResult('nm_computador', 'nm_computador');
     $rsm->addScalarResult('te_node_address', 'te_node_address');
     $rsm->addScalarResult('te_ip_computador', 'te_ip_computador');
     $rsm->addScalarResult('te_ip_rede', 'te_ip_rede');
     $rsm->addScalarResult('nm_rede', 'nm_rede');
     $sql = 'SELECT c.id_computador, c.nm_computador, c.te_node_address, c.te_ip_computador, r.te_ip_rede, r.nm_rede, c.dt_hr_ult_acesso, ';
     foreach ($property as $elm) {
         $sql = $sql . "(CASE WHEN rc.{$elm} IS NOT NULL\n            THEN rc.{$elm}\n            ELSE 'Não identificado'\n            END) as {$elm}, ";
         $rsm->addScalarResult($elm, $elm);
     }
     $size = strlen($sql);
     $sql = substr($sql, 0, $size - 2);
     $sql = $sql . " FROM relatorio_coleta rc\n        INNER JOIN computador c ON rc.id_computador = c.id_computador\n        INNER JOIN rede r ON r.id_rede = c.id_rede\n        WHERE (c.ativo IS NULL or c.ativo = 't')";
     if (!empty($dataInicio)) {
         $sql .= " AND c.dt_hr_ult_acesso >= '{$dataInicio} 00:00:00'";
     }
     if (!empty($dataFim)) {
         $sql .= " AND c.dt_hr_ult_acesso <= '{$dataFim} 23:59:59'";
     }
     $result = $this->getEntityManager()->createNativeQuery($sql, $rsm)->execute();
     return $result;
 }

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

 /**
  * @Route("/overallscore/{id}",name="result_report")
  * 
  */
 public function resultAction($id)
 {
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('amount', 'a');
     $rsm->addScalarResult('total', 't');
     $em = $this->getDoctrine()->getManager();
     $verbalr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'verbal', $rsm);
     $mathr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'mathematical', $rsm);
     $spatialr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'spatial', $rsm);
     $visualr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'visualization', $rsm);
     $classifyr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'classification', $rsm);
     $logicr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'logic', $rsm);
     $patternr = $em->getRepository('SetupBundle:QuizQuestion')->findScoreByType($id, 'pattern recognition', $rsm);
     $verbal = implode(",", $verbalr[0]);
     $math = implode(",", $mathr[0]);
     $spatial = implode(",", $spatialr[0]);
     $visual = implode(",", $visualr[0]);
     $classify = implode(",", $classifyr[0]);
     $logic = implode(",", $logicr[0]);
     $pattern = implode(",", $patternr[0]);
     if (!$verbalr || !$mathr || !$visualr || !$classifyr || !$patternr || !$logicr || !$spatialr) {
         throw $this->createNotFoundException('No Data found for Person');
     }
     return $this->render('ReportBundle::user.html.twig', array('verbal' => $verbal, 'math' => $math, 'visual' => $visual, 'classify' => $classify, 'pattern' => $pattern, 'logic' => $logic, 'spatial' => $spatial, 'name' => 'User Result'));
 }

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

 public function __construct(\Doctrine\ORM\EntityManagerInterface $entityManager)
 {
     parent::__construct($entityManager);
     $resultSetMapping = new ResultSetMapping();
     $resultSetMapping->addScalarResult('ID', 'id');
     $resultSetMapping->addScalarResult('Name', 'name');
     $this->setResultMapping($resultSetMapping);
 }

 public function top20TableSizes()
 {
     $sql = "SELECT nspname || '.' || relname AS relation,\n                  pg_size_pretty(pg_relation_size(C.oid)) AS size\n                FROM pg_class C\n                LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)\n                WHERE nspname NOT IN ('pg_catalog', 'information_schema')\n                  AND C.relkind <> 'i'\n                  AND nspname !~ '^pg_toast'\n                  AND nspname || '.' || relname != 'public.spatial_ref_sys'\n                ORDER BY pg_relation_size(C.oid) DESC\n                LIMIT 20";
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('relation', 'relation');
     $rsm->addScalarResult('size', 'size');
     $query = $this->em->createNativeQuery($sql, $rsm);
     return $query->getResult();
 }

 public function countDifferencesBetweenEnvironment($source, $target)
 {
     $sql = 'select count(*) foundRows from (select r.ouuid from environment_revision e, revision r,  content_type ct where e.environment_id in (' . $source . ' ,' . $target . ') and r.id = e.revision_id and ct.id = r.`content_type_id` and ct.deleted = 0 group by ct.id, r.ouuid, ct.orderKey having count(*) = 1 or max(r.`id`) <> min(r.`id`)) tmp';
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('foundRows', 'foundRows');
     $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
     $foundRows = $query->getResult();
     return $foundRows[0]['foundRows'];
 }

 /**
  * @param $menu
  * @return int
  */
 public function fetchMaxOrder($menu)
 {
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('max_order', 'max_order', 'integer');
     $query = $this->getEntityManager()->createNativeQuery('SELECT MAX(`order`) AS max_order FROM cms_menu AS m WHERE menu = :menu', $rsm);
     $query->setParameter('menu', $menu);
     $res = $query->getOneOrNullResult();
     return $res['max_order'];
 }

 /**
  * return number times one user is into project
  *
  * @param $project
  * @return array
  */
 public function isUserInProject($user, $project)
 {
     $em = $this->getEntityManager();
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('total', 'total');
     $sql = "SELECT COUNT(*) as total FROM users_projects WHERE user_id = " . $user->getId() . " AND project_id = " . $project->getId();
     $query = $em->createNativeQuery($sql, $rsm);
     $result = $query->getResult();
     return $result[0]['total'];
 }

 /** 
  * Checks whether the migration has been run before
  */
 public function isExecuted()
 {
     $em = $this->container->get('Doctrine')->getManager();
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('executed', 'executed');
     $query = $em->createNativeQuery("SELECT executed FROM datamigrations WHERE version=?", $rsm);
     $query->setParameter(1, $this->version);
     $result = $query->getResult();
     return empty($result) ? false : $result[0]['executed'];
 }