/** * @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); }
/** * 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']); }
/** * Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT. * This method is for platforms which DO NOT support ROW_NUMBER. * * @param SelectStatement $AST * @param bool $addMissingItemsFromOrderByToSelect * * @return string * * @throws \RuntimeException */ public function walkSelectStatementWithoutRowNumber(SelectStatement $AST, $addMissingItemsFromOrderByToSelect = true) { // We don't want to call this recursively! if ($AST->orderByClause instanceof OrderByClause && $addMissingItemsFromOrderByToSelect) { // In the case of ordering a query by columns from joined tables, we // must add those columns to the select clause of the query BEFORE // the SQL is generated. $this->addMissingItemsFromOrderByToSelect($AST); } // Remove order by clause from the inner query // It will be re-appended in the outer select generated by this method $orderByClause = $AST->orderByClause; $AST->orderByClause = null; $innerSql = $this->getInnerSQL($AST); $sqlIdentifier = $this->getSQLIdentifier($AST); // Build the counter query $sql = sprintf('SELECT DISTINCT %s FROM (%s) dctrn_result', implode(', ', $sqlIdentifier), $innerSql); // http://www.doctrine-project.org/jira/browse/DDC-1958 $sql = $this->preserveSqlOrdering($sqlIdentifier, $innerSql, $sql, $orderByClause); // Apply the limit and offset. $sql = $this->platform->modifyLimitQuery($sql, $this->maxResults, $this->firstResult); // Add the columns to the ResultSetMapping. It's not really nice but // it works. Preferably I'd clear the RSM or simply create a new one // but that is not possible from inside the output walker, so we dirty // up the one we have. foreach ($sqlIdentifier as $property => $alias) { $this->rsm->addScalarResult($alias, $property); } // Restore orderByClause $AST->orderByClause = $orderByClause; return $sql; }
/** * @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; }
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']; }
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']); }
public function getStatsByMonth() { $rsm = new ResultSetMapping(); $rsm->addScalarResult('m', 'month'); $rsm->addScalarResult('y', 'year'); $rsm->addScalarResult('n', 'number'); $em = $this->getEntityManager(); $query = $em->createNativeQuery(' SELECT MONTH( jlm_core_calendar.dt ) AS m , YEAR( jlm_core_calendar.dt ) AS y, COUNT( transmitters_transmitters.id ) AS n FROM transmitters_transmitters LEFT JOIN transmitters_attributions ON transmitters_attributions.id = transmitters_transmitters.attribution_id LEFT JOIN jlm_core_calendar ON jlm_core_calendar.dt = transmitters_attributions.creation GROUP BY MONTH( jlm_core_calendar.dt ),YEAR(jlm_core_calendar.dt) ORDER BY m,y ', $rsm); return $query->getResult(); }
public function getRanking(Categoria $categoria = null, Usuario $usuario = null) { if (!$categoria) { return false; } $sql = "\n SELECT p.id, p.nombre AS nombre, avg(v.posicion) AS posicion \n FROM participante p \n INNER JOIN categoria c\n ON c.id = p.categoria_id\n AND c.id = " . $categoria->getId() . "\n LEFT JOIN voto v \n ON v.participante_id = p.id \n "; if ($usuario) { $sql .= "AND v.usuario_id = '" . $usuario->getId() . "'"; } $sql .= "\n GROUP BY p.id, p.nombre\n ORDER BY ISNULL(posicion), posicion, p.nombre\n "; $rsm = new ResultSetMapping(); $rsm->addScalarResult('id', 'id'); $rsm->addScalarResult('nombre', 'nombre'); $rsm->addScalarResult('posicion', 'posicion'); $query = $this->getEntityManager()->createNativeQuery($sql, $rsm); return $query->getResult(); }
public function getMonthlySummaryByType($user_id, $operationType, $year) { if ($operationType == 'income') { $operationType = 1; } else { $operationType = 3; } $sql = 'SELECT SUM(o.amount) AS total, YEAR(o.date) AS year, MONTH(o.date) AS month ' . 'FROM operation o ' . 'WHERE o.user_id = ? AND o.operation_type_id = ? AND YEAR(o.date) = ?' . 'GROUP BY YEAR(o.date), MONTH(o.date)'; $rsm = new ResultSetMapping(); $rsm->addScalarResult('total', 'total'); $rsm->addScalarResult('year', 'year'); $rsm->addScalarResult('month', 'month'); $query = $this->_em->createNativeQuery($sql, $rsm); $query->setParameter(1, $user_id); $query->setParameter(2, $operationType); $query->setParameter(3, $year); 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 AST\NewObjectExpression $newObjectExpression * * @return string The SQL. */ public function walkNewObject($newObjectExpression) { $sqlSelectExpressions = array(); $objIndex = $this->newObjectCounter++; foreach ($newObjectExpression->args as $argIndex => $e) { $resultAlias = $this->scalarResultCounter++; $columnAlias = $this->getSQLColumnAlias('sclr'); switch (true) { case ($e instanceof AST\NewObjectExpression): $sqlSelectExpressions[] = $e->dispatch($this); break; default: $sqlSelectExpressions[] = trim($e->dispatch($this)) . ' AS ' . $columnAlias; break; } switch (true) { case ($e instanceof AST\PathExpression): $fieldName = $e->field; $dqlAlias = $e->identificationVariable; $qComp = $this->queryComponents[$dqlAlias]; $class = $qComp['metadata']; $fieldType = $class->getTypeOfField($fieldName); break; case ($e instanceof AST\Literal): switch ($e->type) { case AST\Literal::BOOLEAN: $fieldType = 'boolean'; break; case AST\Literal::NUMERIC: $fieldType = is_float($e->value) ? 'float' : 'integer'; break; } break; default: $fieldType = 'string'; break; } $this->scalarResultAliasMap[$resultAlias] = $columnAlias; $this->rsm->addScalarResult($columnAlias, $resultAlias, $fieldType); $this->rsm->newObjectMappings[$columnAlias] = array( 'className' => $newObjectExpression->className, 'objIndex' => $objIndex, 'argIndex' => $argIndex ); } return implode(', ', $sqlSelectExpressions); }
/** * @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']; }
public function getGeonamePOIsInSquareAction(Request $request, $long1, $lat1, $long2, $lat2) { // First we will protect us from repeated queries from the same user $session = $this->get('session'); $noPolygonsUntil = $session->get('nopolygonsuntil'); $now = new \DateTime(); $session->set('nopolygonsuntil', $now->add(new \DateInterval('PT2S'))); if (isset($noPolygonsUntil) && $now < $noPolygonsUntil) { $session->set('nopolygonsuntil', $now->add(new \DateInterval('PT2S'))); $response = new Response(); $response->setStatusCode(503); return $response; } // Second, we want to normalize the window, to improve cache. This should be done outside controller $long1 = ceil($long1 * 10) / 10; $lat1 = ceil($lat1 * 10) / 10; $long2 = floor($long2 * 10) / 10; $lat2 = floor($lat2 * 10) / 10; $em = $this->getDoctrine()->getManager(); $rsm = new ResultSetMapping(); $rsm->addScalarResult('point', 'point'); $rsm->addScalarResult('name', 'name'); $rsm->addScalarResult('fcode', 'fcode'); $query = $em->createNativeQuery('SELECT name, fcode, ST_AsGeoJSON(coordinate) as point ' . 'FROM public.geoname ' . "WHERE fcode like 'PPL%' " . 'AND st_within(geoname.coordinate, ST_MakeEnvelope(:long1,:lat1,:long2,:lat2,4326)) ' . 'ORDER BY st_distance(coordinate, ST_Centroid(ST_MakeEnvelope(:long1,:lat1,:long2,:lat2,4326))) ASC ' . 'LIMIT 500', $rsm); $query->setParameter('long1', $long1); $query->setParameter('lat1', $lat1); $query->setParameter('long2', $long2); $query->setParameter('lat2', $lat2); $results = $query->getResult(); //dump($results); die(); $features = array(); foreach ($results as $res) { $features[] = array('type' => 'Feature', 'properties' => array('name' => $res['name'], 'fcode' => $res['fcode']), 'geometry' => json_decode($res['point'])); /* $features []= array( 'type'=>'Feature', 'geometry'=>json_decode( $res['point'] ) );*/ } $obj = array('type' => 'FeatureCollection', 'features' => $features); $response = new Response(json_encode($obj)); $response->headers->set('Content-Type', 'application/json'); return $response; }
/** * 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']; }
private function getFileIds($tagIds, $meta) { $sql = $this->craftSql($tagIds, $meta); $rsm = new ResultSetMapping($this->em); $rsm->addScalarResult("file_id", "id"); $list = $this->em->createNativeQuery($sql, $rsm)->getScalarResult(); // clean up the list so it's a flat array of integers return array_map(function ($e) { return (int) $e['id']; }, $list); }