Creates a native SQL query.
public createNativeQuery ( string $sql, Doctrine\ORM\Query\ResultSetMapping $rsm ) : Doctrine\ORM\NativeQuery | ||
$sql | string | |
$rsm | Doctrine\ORM\Query\ResultSetMapping | The ResultSetMapping to use. |
return | Doctrine\ORM\NativeQuery |
/** * Wyszukuje wydarzenia w zadanej odległości od podanych współrzędnych * * @param float $lat * @param float $lng * @param int $distance * @return ArrayObject $events */ public function findEventsInRadius($lat, $lng, $distance = 2) { $query = "\n SELECT\n subSel2.*\n FROM (\n SELECT\n sin(subSel.dlat / 2) * \n sin(subSel.dlat / 2) + \n cos(subSel.lat1) * \n cos(subSel.lat2) * \n sin(subSel.dlng / 2) * \n sin(subSel.dlng / 2) sel,\n subSel.*\n FROM (\n SELECT \n (radians(:lat)-radians(lat)) dlat, \n (radians(:lng)-radians(lng)) dlng, \n radians(lat) lat1, \n radians(lng) lng1,\n radians(:lat) lat2,\n radians(:lng) lng2,\n Event.*\n From \n Event \n ) subSel \n ) subSel2\n WHERE\n (6372.797 * \n (2 * atan2(sqrt(subSel2.sel), sqrt(1 - subSel2.sel)))) <= :distance\n "; $rsm = new ResultSetMappingBuilder($this->entityManger); $rsm->addRootEntityFromClassMetadata('Events\\Entity\\Event', 'event'); $nativeQuery = $this->entityManger->createNativeQuery($query, $rsm); $nativeQuery->setParameters(array('lat' => $lat, 'lng' => $lng, 'distance' => $distance)); return $nativeQuery->getResult(); }
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(); }
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); }
/** * Find all members with a birthday in the next $days days. * * When $days equals 0 or isn't given, it will give all birthdays of today. * * @param int $days The number of days to look ahead. * * @return array Of members sorted by birthday */ public function findBirthdayMembers($days) { // unfortunately, there is no support for functions like DAY() and MONTH() // in doctrine2, thus we have to use the NativeSQL here $builder = new ResultSetMappingBuilder($this->em); $builder->addRootEntityFromClassMetadata('Decision\\Model\\Member', 'm'); $select = $builder->generateSelectClause(array('m' => 't1')); $sql = "SELECT {$select} FROM Member AS t1" . " WHERE DATEDIFF(DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR)," . " DATE_SUB(CURDATE(), INTERVAL YEAR(CURDATE()) YEAR)) BETWEEN 0 AND :days" . " AND t1.expiration >= CURDATE()" . "ORDER BY DATE_SUB(t1.birth, INTERVAL YEAR(t1.birth) YEAR) ASC"; $query = $this->em->createNativeQuery($sql, $builder); $query->setParameter('days', $days); return $query->getResult(); }
public function getFoundRows() { // Run FOUND_ROWS query and add to results array $sql = 'SELECT FOUND_ROWS() AS foundRows'; $rsm = new \Doctrine\ORM\Query\ResultSetMapping(); $rsm->addScalarResult('foundRows', 'foundRows'); $query = $this->em->createNativeQuery($sql, $rsm); $foundRows = $query->getResult(); if ($foundRows[0]['foundRows']) { return $foundRows[0]['foundRows']; } else { return 0; } }
/** * Returns the digest hash for a user. * * @param string $realm * @param string $username * @return string|null */ function getDigestHash($realm, $username) { $rsm = new ResultSetMapping(); $rsm->addScalarResult('digesta1', 'digesta1'); $sql = 'SELECT digesta1 FROM ' . $this->tableName . ' WHERE username = ?'; $query = $this->entityManager->createNativeQuery($sql, $rsm); $query->setParameter(1, $username); $rows = $query->getResult(); if (count($rows)) { return $rows[0]['digesta1']; } else { return null; } }
public function createPagination($query, $page, $limit) { $rsm = new ResultSetMappingBuilder($this->em); $rsm->addScalarResult('count', 'count'); $queryCount = $query->getSql(); $queryCount = "SELECT COUNT(*) as count FROM (" . $queryCount . ") as temp"; $queryCount = $this->em->createNativeQuery($queryCount, $rsm); $queryCount->setParameters($query->getParameters()); $count = $queryCount->getSingleScalarResult(); if (!$count) { $count = 0; } if ($limit === null) { $limit = 20; } if ($page === 'last') { $page = $count / (int) $limit; $page = ceil($page); } if ($page <= 0) { $page = 1; } $query->setHint('knp_paginator.count', $count); $pagination = $this->paginator->paginate($query, (int) $page, $limit, array('distinct' => false)); return $pagination; }
/** * @param string $schema * @param string $oid * @return array */ public function getFunctions($schema = '', $oid = '') { $oidSql = ''; if (!empty($oid)) { $oidSql = " AND p.oid = {$oid}"; } $schemaSql = ''; if (!empty($schema)) { $schemaSql = " AND n.nspname='{$schema}'"; } $sql = "SELECT p.oid,\n proowner as owner,\n n.nspname as schema,\n p.proname as name,\n pg_catalog.pg_get_function_result(p.oid) as \"resultDataType\",\n pg_catalog.pg_get_function_arguments(p.oid) as \"argumentDataTypes\",\n CASE\n WHEN p.proisagg THEN 'agg'\n WHEN p.proiswindow THEN 'window'\n WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'\n ELSE 'normal'\n END as type,\n CASE WHEN not p.proisagg THEN pg_get_functiondef(p.oid) ELSE null END as code\n FROM pg_catalog.pg_proc p\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace\n WHERE pg_catalog.pg_function_is_visible(p.oid) {$oidSql} {$schemaSql}\n ORDER BY 1,2,4"; $rsm = new ResultSetMapping(); $rsm->addScalarResult('oid', 'oid'); $rsm->addScalarResult('owner', 'owner'); $rsm->addScalarResult('schema', 'schema'); $rsm->addScalarResult('name', 'name'); $rsm->addScalarResult('resultDataType', 'resultDataType'); $rsm->addScalarResult('argumentDataTypes', 'argumentDataTypes'); $rsm->addScalarResult('type', 'type'); $rsm->addScalarResult('code', 'code'); $stmt = $this->em->createNativeQuery($sql, $rsm); $functions = []; foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) { $fct = new Fonction($schema); foreach ($row as $key => $value) { $fct->__set($key, $value); } if ($this->index_type == self::INDEX_TYPE_OID) { $functions[$row['oid']] = $fct; } elseif ($this->index_type == self::INDEX_TYPE_NAME) { $functions[$row['name']] = $fct; } } return $functions; }
/** * Creates a native SQL query. * * @param string $queryName * @return NativeQuery */ public function createNativeNamedQuery($queryName) { $queryMapping = $this->_class->getNamedNativeQuery($queryName); $rsm = new Query\ResultSetMappingBuilder($this->_em); $rsm->addNamedNativeQueryMapping($this->_class, $queryMapping); return $this->_em->createNativeQuery($queryMapping['query'], $rsm); }
/** * Updates the list of group members for a group principal. * * The principals should be passed as a list of uri's. * * @param string $principal * @param array $members * @return void * @throws \Sabre\DAV\Exception */ function setGroupMemberSet($principal, array $members) { $rsm = new ResultSetMapping(); $rsm->addScalarResult('id', 'id'); $rsm->addScalarResult('uri', 'uri'); // Grabbing the list of principal id's. $sql = 'SELECT id, uri FROM ' . $this->tableName . ' WHERE uri IN (? ' . str_repeat(', ? ', count($members)) . ')'; $query = $this->entityManager->createNativeQuery($sql, $rsm); $query->setParameters(array_merge([$principal], $members)); $rows = $query->getResult(); $memberIds = []; $principalId = null; foreach ($rows as $row) { if ($row['uri'] == $principal) { $principalId = $row['id']; } else { $memberIds[] = $row['id']; } } if (!$principalId) { throw new DAV\Exception('Principal not found'); } $connection = $this->entityManager->getConnection(); // Wiping out old members $sql = 'DELETE FROM ' . $this->groupMembersTableName . ' WHERE principal_id = ?'; $connection->executeUpdate($sql, [$principalId]); foreach ($memberIds as $memberId) { $sql = 'INSERT INTO ' . $this->groupMembersTableName . ' (principal_id, member_id) VALUES (?, ?)'; $connection->executeUpdate($sql, [$principalId, $memberId]); } }
/** * * @return array */ public function generateExitedJournalData() { $sql = "SELECT count(journal.id) as count FROM journal WHERE status = '" . JournalStatuses::STATUS_EXITED . "'"; $rsm = new ResultSetMapping(); $rsm->addScalarResult('count', 'count'); $query = $this->manager->createNativeQuery($sql, $rsm); $results = $query->getSingleResult(); return $results; }
/** * @param Event $event * @param Constraint $constraint */ public function validate($event, Constraint $constraint) { $eventEndDate = $event->getDate()->format('Y-m-d H:i:s'); $eventEndDate = new \DateTime($eventEndDate); $eventEndDate->modify("+" . $event->getDuration() . " minutes")->format('Y-m-d H:i:s'); // Map result set to my Entity. $rsm = new ResultSetMapping(); $rsm->addEntityResult('AppBundle\\Entity\\Event', 'e'); $rsm->addFieldResult('e', 'termin', 'date'); $rsm->addFieldResult('e', 'dauer', 'duration'); // Using MySQL for better DateTime handling $query = $this->em->createNativeQuery("SELECT * FROM veranstaltung e\n WHERE e.termin < :endDate\n AND e.termin + INTERVAL e.dauer MINUTE > :startDate", $rsm); $query->setParameters(array("endDate" => $eventEndDate, 'startDate' => $event->getDate()->format('Y-m-d H:i:s'))); $overlappingEntities = $query->getResult(); /** @var $constraint HasNoDateOverlap */ if ($overlappingEntities) { $this->context->addViolation($constraint->message); } }
/** * Get all groups of a kind * * @param Kind $kind * @param integer|null $orgId * @return mixed */ public function groupsOfKind(Kind $kind, $orgId = null) { if ($orgId) { $sql = <<<EOT SELECT p.id AS id, p.lastName, g.description, g.avatar FROM groups g INNER JOIN parties p ON g.id = p.id INNER JOIN p2p_relations rel ON p.id = rel.context WHERE p.kind_id = ? AND rel.reference =? ORDER BY p.id EOT; } else { $sql = <<<EOT SELECT p.id AS id, p.firstName, p.lastName, g.description, g.avatar FROM groups g INNER JOIN parties p ON g.id = p.id WHERE p.kind_id = ? ORDER BY p.id EOT; } $rsm = new ResultSetMappingBuilder($this->em); $rsm->addEntityResult('Bakgat\\Notos\\Domain\\Model\\Identity\\Party', 'p'); $rsm->addEntityResult('Bakgat\\Notos\\Domain\\Model\\Identity\\Group', 'g'); $rsm->addFieldResult('p', 'id', 'id'); $rsm->addFieldResult('p', 'lastName', 'lastName'); $rsm->addFieldResult('g', 'description', 'description'); $qb = $this->em->createNativeQuery($sql, $rsm)->setParameter(1, $kind->id()); if ($orgId) { $qb->setParameter(2, $orgId); } $groups = $qb->getScalarResult(); return $groups; }
private function haveTranslation($id) { $sql = <<<SQL SELECT journal_announcement_translations.id FROM journal_announcement_translations WHERE translatable_id = ? SQL; $rsm = new ResultSetMapping(); $rsm->addScalarResult('id', 'id'); $query = $this->em->createNativeQuery($sql, $rsm); $query->setParameter(1, $id); return $query->getResult(); }
/** * Get Paginator * * @param integer $limit ( limit per page ) * @param array $options * @return \Knp\Bundle\PaginatorBundle\Pagination\SlidingPagination */ protected function getPaginator($limit = 30, $options = array()) { $page = $this->get('request')->query->get('page', 1); $cnt = 0; if ($this->get('request')->query->has('page')) { $page = $this->get('request')->query->get('page'); $this->get('session')->set('_pager_' . $this->get('request')->get('_route'), $page); } elseif ($this->get('session')->has('_pager_' . $this->get('request')->get('_route'))) { $page = $this->get('session')->get('_pager_' . $this->get('request')->get('_route')); } if (isset($options['total_item_count']) and (int) $options['total_item_count']) { $cnt = $options['total_item_count']; } $this->paginator = $this->get('knp_paginator'); if (is_array($this->query) or $this->query instanceof QueryBuilder) { $pagination = $this->paginator->paginate($this->query, $page, $limit, $options); } elseif ($this->query instanceof NativeQuery) { $rsm = new ResultSetMapping(); $rsm->addScalarResult('cnt', 'cnt'); $q = strstr($this->query->getSQL(), " FROM "); $q = "SELECT COUNT(*) cnt " . $q; $cntQuery = $this->em->createNativeQuery($q, $rsm)->setParameters($this->query->getParameters()); try { $cnt = $cntQuery->getSingleScalarResult(); } catch (\Doctrine\Orm\NoResultException $e) { $cnt = 0; } $sql = $this->query->getSQL(); $pagination = $this->paginator->paginate(array()); $sort_name = $pagination->getPaginatorOption('sortFieldParameterName'); $sort_direction_name = $pagination->getPaginatorOption('sortDirectionParameterName'); if ($this->get('request')->query->has($sort_name) and $this->get('request')->query->has($sort_direction_name)) { $sql .= ' ORDER BY ' . $this->get('request')->query->get($sort_name) . ' ' . $this->get('request')->query->get($sort_direction_name); } elseif (isset($options['default_sort']) and $options['default_sort']) { $sql .= ' ORDER BY '; foreach ($options['default_sort'] as $field => $type) { $sql .= $field . ' ' . $type . ','; } $sql = trim($sql, ','); } if (!isset($options['not_use_limit_offset'])) { $offset = $limit * ($page - 1); $this->query->setSQL($sql . ' LIMIT ' . $limit . ' OFFSET ' . $offset); } $pagination->setCurrentPageNumber($page); $pagination->setItemNumberPerPage($limit); $pagination->setTotalItemCount($cnt); $pagination->setItems($this->query->getResult()); } $pagination->setTemplate($this->container->getParameter('zk2_admin_panel.pagination_template')); $pagination->setSortableTemplate($this->container->getParameter('zk2_admin_panel.sortable_template')); return compact('pagination'); }
public function normalizeJournalTotalArticleDownload() { $rsm = new ResultSetMapping(); $sql = <<<SQL UPDATE journal SET total_article_download = (SELECT SUM(t2.download_count) FROM article t2 WHERE t2.journal_id = journal.id) SQL; $query = $this->em->createNativeQuery($sql, $rsm); $query->getResult(); }
/** * * @param type $query * @param array $params * @return array */ public function nativeQuery($query, array $params = null) { try { $qr = $this->em->createNativeQuery($query, new \Doctrine\ORM\Query\ResultSetMapping()); if ($params != null) { $qr->setParameter($params); } $result = $qr->getResult(); //$this->em->flush(); return $result; } catch (Exception $exc) { return NULL; } }
public function fillIndexesTables(&$schemas) { $this->em->clear(); //Filling Indexes $sql = "SELECT c2.relname as name,\n c2.relname as oid,\n i.indisprimary as \"isPrimary\",\n i.indisunique as \"isUnique\",\n i.indisclustered as \"isClustered\",\n i.indisvalid as \"isValid\",\n pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) as \"creationQuery\",\n pg_catalog.pg_get_constraintdef(con.oid, true) as \"constraintDef\",\n contype,\n condeferrable,\n condeferred,\n c2.reltablespace,\n c.oid as table,\n n.nspname as schema,\n c.relname as table_name\n FROM pg_catalog.pg_class c\n INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n INNER JOIN pg_catalog.pg_index i ON c.oid = i.indrelid\n INNER JOIN pg_catalog.pg_class c2 ON i.indexrelid = c2.oid\n LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n WHERE c.relkind = 'r'\n AND n.nspname <> 'pg_catalog'\n AND n.nspname <> 'information_schema'\n AND n.nspname !~ '^pg_toast'\n AND n.nspname !~ '^pg_temp'\n AND n.nspname <> 'londiste'\n AND n.nspname <> 'pgq'\n --AND pg_catalog.pg_table_is_visible(c.oid)\n ORDER BY i.indisprimary DESC,\n i.indisunique DESC,\n c2.relname"; $rsm = new ResultSetMapping(); $rsm->addScalarResult('oid', 'oid'); $rsm->addScalarResult('name', 'name'); $rsm->addScalarResult('isPrimary', 'isPrimary'); $rsm->addScalarResult('isUnique', 'isUnique'); $rsm->addScalarResult('isClustered', 'isClustered'); $rsm->addScalarResult('isValid', 'isValid'); $rsm->addScalarResult('creationQuery', 'creationQuery'); $rsm->addScalarResult('constraintDef', 'constraintDef'); $rsm->addScalarResult('contype', 'contype'); $rsm->addScalarResult('condeferrable', 'condeferrable'); $rsm->addScalarResult('condeferred', 'condeferred'); $rsm->addScalarResult('reltablespace', 'reltablespace'); $rsm->addScalarResult('schema', 'schema'); $rsm->addScalarResult('table', 'table'); $rsm->addScalarResult('table_name', 'table_name'); $stmt = $this->em->createNativeQuery($sql, $rsm); $stmt->useResultCache(true, PgRetriever::CACHE_LIFETIME); foreach ($stmt->getResult(AbstractQuery::HYDRATE_ARRAY) as $row) { $index = new Index($row['schema'], $row['table']); foreach ($row as $key => $value) { if ($key == 'creationQuery') { $data = explode('USING', $value); if (count($data) == 2) { $infos = explode(' ', trim($data[1])); $type = $infos[0]; $columns = trim(substr($data[1], strlen($infos[0]) + 2)); $index->__set('type', $type); $index->__set('columns', $columns); } else { $index->__set('type', $value); } } else { $index->__set($key, $value); } } if ($this->index_type == PgRetriever::INDEX_TYPE_OID) { $schemas[$row['schema']]->addTableIndex($row['table'], $index); } elseif ($this->index_type == PgRetriever::INDEX_TYPE_NAME) { $schemas[$row['schema']]->addTableIndex($row['table_name'], $index); } unset($index); } unset($stmt); }
public function prepareTotalCount() { $sql = 'SELECT FOUND_ROWS() AS foundRows'; $rsm = new ResultSetMapping(); $rsm->addScalarResult('foundRows', 'foundRows'); $query = $this->manager->createNativeQuery($sql, $rsm); $foundRows = $query->getResult(); $results['foundRows'] = $foundRows[0]['foundRows']; // echo "<pre>"; // \Doctrine\Common\Util\Debug::dump($results); // echo "</pre>"; // exit; //die("daj".$results['foundRows']); $this->totalCount = (int) $results['foundRows']; // echo "<h1>".$this->totalCount."</h1>"; }
/** * Returns all scheduling objects for the inbox collection. * * These objects should be returned as an array. Every item in the array * should follow the same structure as returned from getSchedulingObject. * * The main difference is that 'calendardata' is optional. * * @param string $principalUri * @return array */ function getSchedulingObjects($principalUri) { $rsm = new ResultSetMapping(); $rsm->addScalarResult('id', 'id'); $rsm->addScalarResult('calendardata', 'calendardata'); $rsm->addScalarResult('uri', 'uri'); $rsm->addScalarResult('lastmodified', 'lastmodified'); $rsm->addScalarResult('etag', 'etag'); $rsm->addScalarResult('size', 'size'); $sql = 'SELECT id, calendardata, uri, lastmodified, etag, size FROM ' . $this->schedulingObjectTableName . ' WHERE principaluri = ?'; $query = $this->entityManager->createNativeQuery($sql, $rsm); $query->setParameter(1, $principalUri); $rows = $query->getResult(); $result = []; foreach ($rows as $row) { $result[] = ['calendardata' => $row['calendardata'], 'uri' => $row['uri'], 'lastmodified' => $row['lastmodified'], 'etag' => '"' . $row['etag'] . '"', 'size' => (int) $row['size']]; } return $result; }
/** * Returns valid IDs for a specific entity with ACL restrictions for current user applied * * @param PermissionDefinition $permissionDef * * @throws InvalidArgumentException * * @return array */ public function getAllowedEntityIds(PermissionDefinition $permissionDef) { $rootEntity = $permissionDef->getEntity(); if (empty($rootEntity)) { throw new InvalidArgumentException("You have to provide an entity class name!"); } $builder = new MaskBuilder(); foreach ($permissionDef->getPermissions() as $permission) { $mask = constant(get_class($builder) . '::MASK_' . strtoupper($permission)); $builder->add($mask); } $query = new Query($this->em); $query->setHint('acl.mask', $builder->get()); $query->setHint('acl.root.entity', $rootEntity); $sql = $this->getPermittedAclIdsSQLForUser($query); $rsm = new ResultSetMapping(); $rsm->addScalarResult('id', 'id'); $nativeQuery = $this->em->createNativeQuery($sql, $rsm); $transform = function ($item) { return $item['id']; }; $result = array_map($transform, $nativeQuery->getScalarResult()); return $result; }
public function createPagination($query, $page, $limit) { $rsm = new ResultSetMappingBuilder($this->em); $rsm->addScalarResult('count', 'count'); // get sql, get the from part and remove all other fields then the id field // so that we have a query who select only one field // for count this is better because we dont need the data $queryCount = $query->getSql(); $queryCountTmp = explode("FROM", $queryCount); $queryCountSelect = array_shift($queryCountTmp); $queryCountEnd = implode("FROM", $queryCountTmp); $queryCountSelect = explode(",", $queryCountSelect); $queryCountSelect = reset($queryCountSelect); if (strpos($queryCountEnd, "GROUP BY") === false) { $queryCount = " SELECT COUNT(*) as count FROM " . $queryCountEnd; } else { $queryCount = "SELECT COUNT(*) as count FROM (" . $queryCountSelect . " FROM " . $queryCountEnd . ") as temp"; } // create now the query based on the native sql query and get the count $queryCount = $this->em->createNativeQuery($queryCount, $rsm); $queryCount->setParameters($query->getParameters()); $count = $queryCount->getSingleScalarResult(); if (!$count) { $count = 0; } if ($page === 'last') { $page = $count / $limit; $page = ceil($page); } if ($page <= 0) { $page = 1; } $query->setHint('knp_paginator.count', $count); $pagination = $this->paginator->paginate($query, (int) $page, $limit, array('distinct' => false)); return $pagination; }
/** * Checks whether the table exists or not. * * @param $tableName * * @return boolean */ private function tableExists(EntityManager $em, $table) { return (bool) $em->createNativeQuery('SHOW TABLE STATUS WHERE Name="' . $table . '" COLLATE utf8_bin ', (new ResultSetMapping())->addScalarResult('Name', 'Name'))->getOneOrNullResult(); }
protected function generateDataValueSummary($timespan) { switch ($timespan) { case 'daily': $entityName = 'UsageStatisticServerBundle:DailyDataValueSummary'; $query = 'YEAR(v.datetime) AS year, MONTH(v.datetime) AS month, DAY(v.datetime) AS day'; $fields = 'year, month, day'; $keys = ['year', 'month', 'day', 'key', 'value']; break; case 'weekly': $entityName = 'UsageStatisticServerBundle:WeeklyDataValueSummary'; $query = 'YEAR(v.datetime) AS year, WEEKOFYEAR(v.datetime) AS week'; $fields = 'year, week'; $keys = ['year', 'week', 'key', 'value']; break; case 'monthly': $entityName = 'UsageStatisticServerBundle:MonthlyDataValueSummary'; $query = 'YEAR(v.datetime) AS year, MONTH(v.datetime) AS month'; $fields = 'year, month'; $keys = ['year', 'month', 'key', 'value']; break; case 'quarterly': $entityName = 'UsageStatisticServerBundle:QuarterlyDataValueSummary'; $query = 'YEAR(v.datetime) AS year, QUARTER(v.datetime) AS quarter'; $fields = 'year, quarter'; $keys = ['year', 'quarter', 'key', 'value']; break; case 'yearly': $entityName = 'UsageStatisticServerBundle:YearlyDataValueSummary'; $query = 'YEAR(v.datetime) AS year'; $fields = 'year'; $keys = ['year', 'key', 'value']; break; default: throw new \InvalidArgumentException(); } $mapping = new ResultSetMapping(); $mapping->addScalarResult('year', 'year'); $mapping->addScalarResult('month', 'month'); $mapping->addScalarResult('quarter', 'quarter'); $mapping->addScalarResult('week', 'week'); $mapping->addScalarResult('day', 'day'); $mapping->addScalarResult('key_name', 'key'); $mapping->addScalarResult('value', 'value'); $mapping->addScalarResult('summary', 'summary'); $sql = <<<SQL SELECT {$fields}, key_name, value, COUNT(id) AS summary FROM ( \tSELECT {$query}, i.id, v.key_name, v.value \tFROM data_values v \tINNER JOIN installations i \tON i.id = v.installation \tGROUP BY {$fields}, i.id, v.key_name, v.value ) t GROUP BY {$fields}, key_name, value SQL; $query = $this->entityManager->createNativeQuery($sql, $mapping); $result = $query->getResult(); $repository = $this->entityManager->getRepository($entityName); $class = new \ReflectionClass($repository->getClassName()); $propertyAccessor = new PropertyAccessor(); $this->truncate($repository); foreach ($result as $row) { $entity = $class->newInstance(); foreach ($keys as $key) { $propertyAccessor->setValue($entity, $key, $row[$key]); } $entity->setSummary($row['summary']); $this->entityManager->persist($entity); if ($this->isFlushRequired()) { $this->entityManager->flush(); $this->entityManager->clear(); } } $this->entityManager->flush(); }
/** * Check whether the usr table has a nonce column or not. * * @param EntityManager $em * * @return boolean */ private function hasField(EntityManager $em, $fieldName) { return (bool) $em->createNativeQuery("SHOW FIELDS FROM usr WHERE Field = :field", (new ResultSetMapping())->addScalarResult('Field', 'Field'))->setParameter(':field', $fieldName)->getOneOrNullResult(); }
/** * {@inheritdoc} */ public function createNativeQuery($sql, ResultSetMapping $rsm) { return $this->wrapped->createNativeQuery($sql, $rsm); }
/** * pings database to keep connection alive * * @param EntityManager $manager */ protected function pingDatabase($manager) { $tmp = $manager->createNativeQuery('SELECT 1', new ResultSetMapping())->getResult(); unset($tmp); $manager->clear(); }
private function assertUsrTableIsSanitized(EntityManager $em) { $rs = $em->createNativeQuery("SHOW FIELDS FROM usr_backup WHERE Field = 'usr_login';", (new ResultSetMapping())->addScalarResult('Type', 'Type'))->getSingleResult(); $this->assertSame('varchar(128)', strtolower($rs['Type'])); }
/** * {@inheritDoc} * * @static */ public static function createNativeQuery($sql, $rsm) { return \Doctrine\ORM\EntityManager::createNativeQuery($sql, $rsm); }
/** * @param InputInterface $input * @param OutputInterface $output * * @return void */ protected function execute(InputInterface $input, OutputInterface $output) { $this->io->title($this->getDescription()); $totalAuthorCount = $this->getAuthorCount(); $this->io->progressStart($totalAuthorCount); $rsm = new ResultSetMapping(); for ($count = 0; $count <= $totalAuthorCount; $count += self::STEP) { $sql = <<<SQL UPDATE author SET user_id = users.id FROM users WHERE author.email = users.email AND author.id > ? and author.id < ? and author.user_id is null SQL; $query = $this->em->createNativeQuery($sql, $rsm); $query->setParameter(1, $count); $query->setParameter(2, $count + self::STEP); $query->getResult(); if (self::STEP > $totalAuthorCount) { $this->io->progressFinish(); } else { $this->io->progressAdvance(self::STEP); } } $this->io->newLine(2); $this->io->success('All process finished'); }