/** * @param QueryInterface $query * @return array */ public function fetch(QueryInterface $query) { $query->checkReplacements(); $plainQuery = $query->getPlainQuery(); list($parameters, $plainQuery) = $this->modifyParametersFromArrayToScalar($query->getParameters(), $plainQuery); return $this->connection->fetchAll($plainQuery, $parameters); }
protected function getRecentFrequent(User $user, array $criteria = null, $limit = 10) { $sql = ' SELECT text, MAX(createdAt) AS recency, COUNT(*) AS frequency FROM stress_log_factors JOIN stress_logs ON stress_log_id = stress_logs.id WHERE user_id = ? '; $params = array($user->getId()); if (is_array($criteria)) { foreach ($criteria as $key => $val) { $op = '='; if (is_array($val)) { reset($val); $op = key($val); $val = current($val); } $sql .= 'AND `' . $key . '` ' . $op . ' ? '; $params[] = $val; } } $sql .= 'GROUP BY text '; $sql .= 'ORDER BY recency DESC, frequency DESC '; $sql .= 'LIMIT ' . (int) $limit; $result = $this->conn->fetchAll($sql, $params); return array_column($result, 'text'); }
/** * Получить продукты по ID категории * * @param $categoryId ID категории * * @return array */ public function getProductsByCategoryId($categoryId) { return $this->db->fetchAll('SELECT `products`.* FROM `products` INNER JOIN `products_categories` ON `products`.`id` = `products_categories`.`product_id` WHERE `products_categories`.`category_id` = ?', array($categoryId)); }
public function exportData($export, $reporter) { $this->conn->beginTransaction(); try { $lastExportedAt = (int) $export['lastExportedAt']; $areas = $this->conn->fetchAll('SELECT a.`id`, a.`name`, t.`id` AS `territoryId`, t.`name` AS `territoryName`, a.`customData`, a.`lastUpdatedAt` ' . 'FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . CoreTables::TERRITORY_TBL . '` t ON t.`id` = a.`territoryId` ' . 'WHERE a.`projectId` = :projectId AND a.`statusId` = :statusId', [':projectId' => $export['projectId'], ':statusId' => $export['areaStatusId']]); $block = new ExportBlock(); foreach ($areas as $area) { $block->addId($area['id']); if ($area['lastUpdatedAt'] > $lastExportedAt) { $area['customData'] = json_decode($area['customData']); $block->addUpdatedId($area['id']); $block->addUpdate($area); } } $event = new ExportEvent($export['projectId'], $export['lastExportedAt'], $reporter); $event->addBlock('area', $block); $event = $this->eventDispatcher->dispatch(ExportEvents::EXPORT_ONGOING, $event); $this->conn->executeQuery('UPDATE `' . ExportTables::DATA_EXPORT_TBL . '` SET `lastExportedAt` = :time WHERE `id` = :id', [':time' => time(), ':id' => $export['id']]); $this->conn->commit(); return $event->output(); } catch (Exception $ex) { $this->conn->rollBack(); throw $ex; } }
public function findAll() { $result = $this->db->fetchAll("select * from " . $this->table . ";"); $event = new GenericEvent($result); $this->eventDispatcher->dispatch("post-findall-" . $this->entityName, $event); return $event->getSubject(); }
/** * Dispatches magic methods (findBy[Property]()) * * @param string $methodName The name of the magic method * @param string $arguments The arguments of the magic method * @throws \Exception * @return mixed * @api */ public function __call($methodName, $arguments) { if (substr($methodName, 0, 6) === 'findBy' && strlen($methodName) > 7) { $propertyName = lcfirst(substr($methodName, 6)); $sql = "select * from " . $this->tableName . " where " . $propertyName . " = :propertyValue order by id desc"; $result = $this->db->fetchAll($sql, array('propertyValue' => $arguments[0])); // Convert query result to an array of objects $objectList = array(); foreach ($result as $row) { $id = $row['id']; $objectList[$id] = $this->buildDomainObject($row); } return $objectList; } elseif (substr($methodName, 0, 9) === 'findOneBy' && strlen($methodName) > 10) { $propertyName = lcfirst(substr($methodName, 9)); $sql = "select * from " . $this->tableName . " where id = :id"; $row = $this->db->fetchAssoc($sql, array('id' => $arguments[0])); if ($row) { return $this->buildDomainObject($row); } else { throw new \Exception("No " . $this->objectName . " matching " . $propertyName . " " . $arguments[0]); } } elseif (substr($methodName, 0, 7) === 'countBy' && strlen($methodName) > 8) { $propertyName = lcfirst(substr($methodName, 7)); $sql = "select COUNT(id) from " . $this->tableName . " where " . $propertyName . " = :propertyValue order by id desc"; $result = current($this->db->fetchAssoc($sql, array('propertyValue' => $arguments[0]))); return $result; } throw new \Exception('The method "' . $methodName . '" is not supported by the repository.', 1233180480); }
public function indexAction() { // $this->init($app); $posts = $this->db->fetchAll('SELECT * FROM post'); //var_dump($posts); return $this->twig->render('post/index.twig', array('posts' => $posts)); }
/** * @param LoggerInterface $logger * @param bool $dryRun */ protected function doExecute(LoggerInterface $logger, $dryRun = false) { $duplicateEntitiesQuery = 'SELECT DISTINCT t2.id FROM orocrm_campaign_email_stats AS t1 LEFT JOIN orocrm_campaign_email_stats AS t2 ON t1.email_campaign_id = t2.email_campaign_id AND t1.marketing_list_item_id = t2.marketing_list_item_id AND t2.id > t1.id WHERE t2.id IS NOT NULL'; // Done in 2 queries for cross DB support. $idsToRemove = array_map(function ($item) { if (is_array($item) && array_key_exists('id', $item)) { return $item['id']; } return null; }, $this->connection->fetchAll($duplicateEntitiesQuery)); if ($idsToRemove) { $query = 'DELETE FROM orocrm_campaign_email_stats WHERE id IN (?)'; $logger->notice($query); if (!$dryRun) { $this->connection->executeQuery($query, [$idsToRemove], [Connection::PARAM_INT_ARRAY]); } } }
public function collectData(IdentifiableInterface $root) { $routes = $this->conn->fetchAll('SELECT `routeAscent` ' . 'FROM `' . EdkTables::ROUTE_TBL . '` r ' . 'INNER JOIN `' . CoreTables::AREA_TBL . '` a ON r.`areaId` = a.`id` ' . 'WHERE a.`projectId` = :projectId AND r.`routeType` = 0', [':projectId' => $root->getId()]); if (sizeof($routes) == 0) { return false; } foreach ($routes as &$row) { $row['normalized'] = $this->step($row['routeAscent']); } $this->data = []; $min = 10000; $max = 0; foreach ($routes as $row) { if ($row['normalized'] < $min) { $min = $row['normalized']; } if ($row['normalized'] > $max) { $max = $row['normalized']; } } if ($min >= $max) { return false; } for ($i = $min; $i <= $max; $i++) { $this->data[$i] = 0; } foreach ($routes as $row) { $this->data[$row['normalized']]++; } return true; }
/** * Returns all records from this repository's table * * @param integer $limit * * @return array */ public function findAll($limit = null) { if (null === $limit) { return $this->db->fetchAll(sprintf('SELECT * FROM %s', $this->getTableName())); } return $this->db->fetchAll(sprintf('SELECT * FROM %s LIMIT %d', $this->getTableName(), $limit)); }
/** * @param $messageHandlerId * @return array of task data */ public function findTasksOfMessageHandler($messageHandlerId) { $tasks = $this->connection->fetchAll('SELECT * FROM ' . Tables::TASK . ' WHERE message_handler_id = :mhid', ['mhid' => $messageHandlerId]); foreach ($tasks as &$task) { $this->fromDatabase($task); } return $tasks; }
/** * @return Exercise[] */ public function getAll() { $exercises = []; foreach ($this->connection->fetchAll('SELECT id, title, description FROM exercises') as $data) { $exercises[] = new Exercise($data['id'], $data['title'], $data['description']); } return $exercises; }
private function getTableNames() { $tableNames = []; foreach ($this->connection->fetchAll('SHOW TABLES') as $row) { $tableNames[] = $row['Tables_in_' . $this->connection->getDatabase()]; } return $tableNames; }
/** * @param string $organisation * @param string $project * * @return array */ public function getProjects($organisation, $project) { if (false === empty($project)) { return [$project]; } $projects = $this->connection->fetchAll('SELECT project FROM events WHERE organisation = ? GROUP BY project', [$organisation]); return array_column($projects, 'project'); }
/** * @param Contract $streamContract * @param Identifier $streamId * @return EventEnvelope[] */ public function fetch(Contract $streamContract, Identifier $streamId) { $records = $this->connection->fetchAll(Query\Select::from(self::TABLE_NAME), ['streamContract' => $streamContract, 'streamId' => $streamId]); $eventEnvelopes = array_map(function (array $record) { return EventEnvelope::reconstitute(EventId::fromString($record['eventId']), Contract::with($record['eventContract']), $record['eventPayload']); }, $records); return $eventEnvelopes; }
/** * Fetch all * * @return array */ public function fetchAll() { $tableName = $this->connection->quoteIdentifier($this->tableName); $sql = "SELECT version FROM {$tableName} ORDER BY version ASC"; $all = $this->connection->fetchAll($sql); return array_map(function ($v) { return $v['version']; }, $all); }
private function buildRepresentations(User $user) { $projects = $this->conn->fetchAll('SELECT g.`id`, g.`name`, g.`slug`, c.`role`, c.`note` FROM `' . CoreTables::GROUP_TBL . '` g ' . 'INNER JOIN `' . CoreTables::GROUP_MEMBER_TBL . '` c ON c.`groupId` = g.`id` ' . 'WHERE c.`userId` = :userId ORDER BY g.`name`', [':userId' => $user->getId()]); $items = array(); foreach ($projects as $proj) { $items[] = new ProjectRepresentation($proj['slug'], $proj['name'], 'group_dashboard', 'GroupNominative: 0', 'default', $this->resolver->getRole('Group', $proj['role']), $proj['note']); } return $items; }
private function buildRepresentations(User $user) { $projects = $this->conn->fetchAll('SELECT a.`id`, a.`name`, a.`slug`, c.`role`, c.`note` FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . CoreTables::AREA_MEMBER_TBL . '` c ON c.`areaId` = a.`id` ' . 'WHERE c.`userId` = :userId ORDER BY a.`name`', [':userId' => $user->getId()]); $items = array(); foreach ($projects as $proj) { $items[] = new ProjectRepresentation($proj['slug'], $proj['name'], 'area_dashboard', 'AreaNominative: 0', 'purple', $this->resolver->getRole('Area', $proj['role']), $proj['note']); } return $items; }
public function findAvailableCourses(User $user) { $items = $this->conn->fetchAll('SELECT c.`id`, c.`name`, c.`deadline`, r.`result` AS `user_result`, r.`passedQuestions` AS `user_passedQuestions`, ' . 'r.`totalQuestions` AS `user_totalQuestions`, r.`completedAt` AS `user_completedAt`, arr.`result` AS `area_result`, arr.`passedQuestions` AS `area_passedQuestions`, ' . 'arr.`totalQuestions` AS `area_totalQuestions`, arr.`completedAt` AS `area_completedAt` ' . 'FROM `' . CourseTables::COURSE_TBL . '` c ' . 'LEFT JOIN `' . CourseTables::COURSE_AREA_RESULT_TBL . '` ar ON (ar.`courseId` = c.`id` AND ar.`areaId` = :areaId) ' . 'LEFT JOIN `' . CourseTables::COURSE_RESULT_TBL . '` arr ON (arr.`courseId` = ar.`courseId` AND arr.`userId` = ar.`userId`) ' . 'LEFT JOIN `' . CourseTables::COURSE_RESULT_TBL . '` r ON (r.`courseId` = c.`id` AND r.`userId` = :userId) ' . 'WHERE c.`isPublished` = 1 AND c.`projectId` = :projectId ORDER BY c.`displayOrder`', [':areaId' => $this->area->getId(), ':userId' => $user->getId(), ':projectId' => $this->area->getProject()->getId()]); foreach ($items as &$item) { TestResult::processResults($item, 'user_'); TestResult::processResults($item, 'area_'); } return $items; }
public function getApplied() { $appliedMigrations = $this->connection->fetchAll(sprintf('SELECT * FROM %s', $this->migrationTableName)); $applied = []; foreach ($appliedMigrations as $item) { $applied[] = $item['name']; } return $applied; }
public function findMilestoneProgressForAreasInGroup(Group $group) { $totalMilestones = $this->conn->fetchColumn('SELECT COUNT(`id`) FROM `' . MilestoneTables::MILESTONE_TBL . '` WHERE `projectId` = :projectId AND `entityType` = \'Area\'', [':projectId' => $group->getProject()->getId()]); $results = $this->conn->fetchAll('SELECT a.`id`, a.`name`, a.`entityId`, p.`completedNum` ' . 'FROM `' . CoreTables::AREA_TBL . '` a ' . 'INNER JOIN `' . MilestoneTables::MILESTONE_PROGRESS_TBL . '` p ON p.`entityId` = a.`entityId` ' . 'WHERE a.`groupId` = :groupId ' . 'ORDER BY p.`completedNum` DESC, a.`name`', [':groupId' => $group->getId()]); foreach ($results as &$result) { $this->processResult($totalMilestones, $result); } return $results; }
/** * @param int $limit * @param int $offset * @return array */ public function findAll(int $limit, int $offset = 0) : array { $qb = $this->connection->createQueryBuilder(); $qb->select('*')->from('dumplie_inventory_product')->setFirstResult($offset)->setMaxResults($limit); $results = $this->connection->fetchAll($qb->getSQL(), $qb->getParameters()); return array_map(function ($data) { return new Product($data['sku'], $data['price_amount'] / $data['price_precision'], $data['price_currency'], (bool) $data['is_in_stock'], $this->mao->getBy([Metadata::FIELD_SKU => $data['sku']])); }, $results); }
/** * @return array<string,int> */ public function findCategories() { $sql = 'SELECT p_category, count(*) as products FROM shop_products GROUP BY p_category'; $rows = $this->conn->fetchAll($sql); $categories = array(); foreach ($rows as $row) { $categories[$row['p_category']] = $row['products']; } return $categories; }
public function fetchMentors() { $data = $this->dbal->fetchAll('SELECT * FROM users WHERE isMentor = 1'); $users = []; foreach ($data as $userData) { $users[] = $this->hydrator->hydrate($userData, $user = new User()); $this->in_memory_users[$user->getId()] = $user; } return $users; }
/** * Updates the statistics for area requests in the current day. * * @param \Cantiga\CoreBundle\Repository\AreaRequestEvent $event */ public function onAreaRequestStatusChange(AreaRequestEvent $event) { $project = $event->getAreaRequest()->getProject(); $values = [0 => 0, 1 => 0, 2 => 0, 3 => 0]; $calculated = $this->conn->fetchAll('SELECT `status`, COUNT(`id`) AS `counted` FROM `' . CoreTables::AREA_REQUEST_TBL . '` WHERE `projectId` = :projectId GROUP BY `status`', [':projectId' => $project->getId()]); foreach ($calculated as $row) { $values[$row['status']] = $row['counted']; } $date = date('Y-m-d'); $this->conn->executeQuery('INSERT INTO `' . CoreTables::STAT_ARQ_TIME_TBL . '` (`projectId`, `datePoint`, `requestsNew`, `requestsVerification`, `requestsApproved`, `requestsRejected`)' . 'VALUES(:projectId, :datePoint, :rn1, :rv1, :ra1, :rr1) ON DUPLICATE KEY UPDATE `requestsNew` = :rn2, `requestsVerification` = :rv2, `requestsApproved` = :ra2, `requestsRejected` = :rr2', [':projectId' => $project->getId(), ':datePoint' => $date, ':rn1' => $values[AreaRequest::STATUS_NEW], ':rv1' => $values[AreaRequest::STATUS_VERIFICATION], ':ra1' => $values[AreaRequest::STATUS_APPROVED], ':rr1' => $values[AreaRequest::STATUS_REVOKED], ':rn2' => $values[AreaRequest::STATUS_NEW], ':rv2' => $values[AreaRequest::STATUS_VERIFICATION], ':ra2' => $values[AreaRequest::STATUS_APPROVED], ':rr2' => $values[AreaRequest::STATUS_REVOKED]]); }
/** * Get all custom components of the plugin. Is cached per request * * @return mixed */ public function getCustomComponents() { if ($this->components) { return $this->components; } $components = $this->connection->fetchAll('SELECT * FROM `s_library_component` WHERE pluginID = ?', [$this->pluginId]); foreach ($components as $component) { $this->components[$component['cls']] = $component; } return $this->components; }
/** * get a user by its username * * @param $username * @return null|User */ public function getUserByUsername($username) { $sql = "SELECT * FROM users WHERE username=:username LIMIT 0,1"; $params = ['username' => $username]; $data = $this->conn->fetchAll($sql, $params); if ($data) { $user = $this->hydrateUser($data[0]); return $user; } return null; }
/** * Return a list of all articles, sorted by date (most recent first). * * @return array A list of all articles. */ public function findAll() { $sql = "SELECT * FROM articles"; $result = $this->db->fetchAll($sql); $articles = array(); foreach ($result as $row) { $articleId = $row['id']; $articles[$articleId] = $this->buildArticle($row); } return $articles; }
/** * Return a list of all articles, sorted by date (most recent first). * * @return array A list of all articles. */ public function findAll() { $sql = "select * from t_article order by art_id desc"; $result = $this->db->fetchAll($sql); // Convert query result to an array of domain objects $articles = array(); foreach ($result as $row) { $articleId = $row['art_id']; $articles[$articleId] = $this->buildArticle($row); } return $articles; }
/** * Return a list of all movies, sorted by date (most recent first). * * @return array A list of all movies. */ public function findAll() { $sql = "select * from movie order by mov_id desc"; $result = $this->db->fetchAll($sql); // Convert query result to an array of domain objects $movies = array(); foreach ($result as $row) { $movieId = $row['mov_id']; $movies[$movieId] = $this->buildMovie($row); } return $movies; }