PHP ResultSetMapping::addScalarResult方法代码示例

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

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

 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 __construct(\Doctrine\ORM\EntityManagerInterface $entityManager)
 {
     parent::__construct($entityManager);
     $resultSetMapping = new ResultSetMapping();
     $resultSetMapping->addScalarResult('DestinationNumber', 'receiver');
     $resultSetMapping->addScalarResult('TextDecoded', 'message');
     $resultSetMapping->addScalarResult('Status', 'status');
     $resultSetMapping->addScalarResult('SenderID', 'phone');
     $this->setResultMapping($resultSetMapping);
 }

 /**
  * Lists all Empleado entities.
  *
  * @Route("/", name="admin_empleado")
  * @Method("GET")
  * @Template()
  */
 public function indexAction()
 {
     $entity = new Empleado();
     $form = $this->createCreateForm($entity);
     $rsm = new ResultSetMapping();
     $em = $this->getDoctrine()->getManager();
     $sql = "select per.nombres as pnombre, per.apellidos as papellido,  " . "per.direccion as direccion, per.telefono as tel, per.email as email, emp.id as idemp, emp.cargo as cargo, emp.foto as foto,  emp.estado as estado " . "from empleado emp inner join persona per on emp.persona = per.id where emp.estado=true  order by per.apellidos";
     $rsm->addScalarResult('idemp', 'idemp');
     $rsm->addScalarResult('pnombre', 'pnombre');
     //$rsm->addScalarResult('snombre','snombre');
     $rsm->addScalarResult('papellido', 'papellido');
     //$rsm->addScalarResult('sapellido','sapellido');
     //$rsm->addScalarResult('casada','casada');
     $rsm->addScalarResult('direccion', 'direccion');
     $rsm->addScalarResult('tel', 'tel');
     $rsm->addScalarResult('email', 'email');
     $rsm->addScalarResult('cargo', 'cargo');
     $rsm->addScalarResult('foto', 'foto');
     //$rsm->addScalarResult('sucursal','sucursal');
     $rsm->addScalarResult('estado', 'estado');
     $empleados = $em->createNativeQuery($sql, $rsm)->getResult();
     $usuario = $this->get('security.token_storage')->getToken()->getUser();
     $foto = $usuario->getPersona()->getEmpleado()[0]->getFoto();
     return array('empleados' => $empleados, 'entity' => $entity, 'form' => $form->createView(), 'foto' => $foto);
 }

 /**
  *
  * 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 findEntityUsersRight($entity, $em)
 {
     $entityName = get_class($entity);
     $entityId = $entity->getId();
     $tables = array('acl_classes' => $this->container->getParameter('security.acl.dbal.class_table_name'), 'acl_security_identities' => $this->container->getParameter('security.acl.dbal.sid_table_name'), 'acl_object_identities' => $this->container->getParameter('security.acl.dbal.oid_table_name'), 'acl_object_ancestors' => $this->container->getParameter('security.acl.dbal.oid_ancestors_table_name'), 'acl_entries' => $this->container->getParameter('security.acl.dbal.entry_table_name'));
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('identifier', 'identifier');
     $rsm->addScalarResult('mask', 'mask');
     $query = $em->createNativeQuery('SELECT SI.IDENTIFIER,E.MASK ' . 'FROM ' . $tables['acl_classes'] . ' C ' . 'JOIN ' . $tables['acl_object_identities'] . ' OI ON C.ID = OI.CLASS_ID ' . 'JOIN ' . $tables['acl_entries'] . ' E ON E.CLASS_ID = C.ID AND E.OBJECT_IDENTITY_ID = OI.ID ' . 'JOIN ' . $tables['acl_security_identities'] . ' SI ON SI.ID = E.SECURITY_IDENTITY_ID ' . 'WHERE C.CLASS_TYPE = ? ' . 'AND OI.OBJECT_IDENTIFIER = ? ' . 'ORDER BY SI.IDENTIFIER', $rsm);
     $query->setParameter(1, $entityName);
     $query->setParameter(2, $entityId);
     $result = array();
     $users = array();
     $previousIdentifier = null;
     foreach ($query->getResult() as $i => $row) {
         if (strpos($row['identifier'], '-') === FALSE) {
             $login = $row['identifier'];
             $result[$login]['type'] = 'role';
         } else {
             $login = substr($row['identifier'], strpos($row['identifier'], '-') + 1);
             $result[$login]['type'] = 'user';
         }
         if (!array_key_exists($login, $result)) {
             $result[$login] = array();
         }
         $result[$login]['masks'][] = $row['mask'];
         $result[$login]['user'] = array();
         $result[$login]['login'] = $login;
         // $result[$login]['mask']      = $row['mask'];
         $result[$login]['rights'][] = 'role.mask.' . $row['mask'];
         $result[$login]['descriptions'][] = 'role.rightdescription.' . $row['mask'];
         $users[] = $result[$login]['login'];
     }
     if ($users != null) {
         $qb = $em->createQueryBuilder();
         $qb->add('select', 'u.id,u.username,u.firstName,u.lastName,u.email,u.locked')->add('from', 'LowbiSystemBundle:User u');
         $qb->add('where', $qb->expr()->in('u.username', '?1'));
         $qb->setParameter(1, $users);
         $query = $qb->getQuery();
         $userresult = $query->getResult();
         $usersArray = array();
         foreach ($userresult as $item) {
             $usersArray[$item['username']] = $item;
         }
         foreach ($result as $key => $item) {
             if ($result[$key]['type'] == 'user') {
                 $result[$key]['user'] = $usersArray[$item['login']];
             }
         }
         return $result;
     } else {
         return null;
     }
 }

 /**
  * @group DDC-407
  */
 public function testHydrateScalarResults()
 {
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('foo1', 'foo');
     $rsm->addScalarResult('bar2', 'bar');
     $rsm->addScalarResult('baz3', 'baz');
     $resultSet = array(array('foo1' => 'A', 'bar2' => 'B', 'baz3' => 'C'));
     $stmt = new HydratorMockStatement($resultSet);
     $hydrator = new \Doctrine\ORM\Internal\Hydration\ScalarHydrator($this->_em);
     $result = $hydrator->hydrateAll($stmt, $rsm);
 }

 public function __construct(\Doctrine\ORM\EntityManagerInterface $entityManager)
 {
     parent::__construct($entityManager);
     $resultSetMapping = new ResultSetMapping();
     $resultSetMapping->addScalarResult('ID', 'id');
     $resultSetMapping->addScalarResult('SenderNumber', 'sender');
     $resultSetMapping->addScalarResult('TextDecoded', 'message');
     $resultSetMapping->addScalarResult('Processed', 'processed');
     $resultSetMapping->addScalarResult('RecipientID', 'phone');
     $this->setResultMapping($resultSetMapping);
 }

    private function getJournals()
    {
        $sql = <<<SQL
        SELECT id,footer_text FROM journal WHERE journal.footer_text is not null
SQL;
        $rsm = new ResultSetMapping();
        $rsm->addScalarResult('id', 'id');
        $rsm->addScalarResult('footer_text', 'text');
        $query = $this->em->createNativeQuery($sql, $rsm);
        return $query->getResult();
    }

 public function testFindByNativeCQL()
 {
     $em = $this->getEntityManager();
     $rsm = new ResultSetMapping();
     $rsm->addScalarResult('name', 'name');
     $rsm->addScalarResult('price', 'price');
     $query = $em->createNativeQuery('SELECT * FROM product WHERE name = ?', $rsm);
     $query->setParameter(1, 'prod2');
     $products = $query->getResult();
     $this->assertNotEmpty($products);
     $this->assertInternalType('array', $products[0]);
 }

 /**
  *
  *   get contentious points by the create event
  *
  **/
 public function contentiousPointsByCreateEventId($createEventId)
 {
     if (empty($createEventId)) {
         throw new \InvalidArgumentException('createEventId may not be empty');
     }
     $sql = "SELECT ST_X(point) as x, ST_Y(point) as y FROM contentious_point WHERE create_event_id = :createEventId";
     $rsm = new \Doctrine\ORM\Query\ResultSetMapping();
     $rsm->addScalarResult('x', 'x');
     $rsm->addScalarResult('y', 'y');
     $query = $this->em->createNativeQuery($sql, $rsm);
     $query->setParameter('createEventId', $createEventId);
     return $query->getResult();
 }

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

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