/** * @param QueryBuilder $queryBuilder * @param $callback * @param int|null $limit * @param int $maxAttempts * @throws DBALWalkerException * @throws DBALException */ public function run(QueryBuilder $queryBuilder, $callback, $limit = null, $maxAttempts = 10) { if (!is_callable($callback)) { throw new DBALWalkerException('$callback is not callable'); } if (!is_null($limit)) { $queryBuilder->setMaxResults($limit); } else { $limit = $queryBuilder->getMaxResults(); } if (is_null($limit)) { $limit = PHP_INT_MAX; } $offset = 0; do { $rows = []; $errors = $maxAttempts; while (true) { try { $rows = $queryBuilder->setFirstResult($offset)->execute()->fetchAll(); break; } catch (DBALException $e) { if ($errors-- == 0) { throw $e; } $queryBuilder->getConnection()->connect(); } } call_user_func($callback, $rows, $offset, $limit); $offset += $limit; } while (count($rows) >= $limit); }
/** * @internal */ private function incorporateDbalQueryBuilder(QueryBuilder $qb, FilterInterface $filter) { $criteria = $this->getFilteringCriteria($filter); // extraindo os rootAliases, pois o DBAL\QueryBuilder não tem $fromPart = $qb->getQueryPart('from'); $rootAliases = array(); foreach ($fromPart as $part) { $rootAliases[] = $part['alias']; } $visitor = new DbalQueryExpressionVisitor($qb->getConnection(), $rootAliases, $this->fieldMap); if ($whereExpression = $criteria->getWhereExpression()) { $qb->andWhere($visitor->dispatch($whereExpression)); $qb->setParameters($visitor->getParameters()); } if ($criteria->getOrderings()) { foreach ($criteria->getOrderings() as $sort => $order) { $qb->addOrderBy($visitor->getFieldName($sort), $order); } } if (($firstResult = $criteria->getFirstResult()) !== null) { $qb->setFirstResult($firstResult); } if (($maxResults = $criteria->getMaxResults()) !== null) { $qb->setMaxResults($maxResults); } }
/** * Get platform specific group_concat token for provided column * * @param string $alias * @param QueryBuilder $query * * @return string */ protected function getPlatformGroupConcat($alias, QueryBuilder $query) { $platform = $query->getConnection()->getDatabasePlatform()->getName(); $field = $this->mapping['fieldname']; $dummy = 'f_' . $field; switch ($platform) { case 'mysql': return "GROUP_CONCAT(DISTINCT CONCAT_WS('_', " . $dummy . '.name, ' . $dummy . '.grouping, ' . $dummy . ".id)) as {$alias}"; case 'sqlite': return 'GROUP_CONCAT(DISTINCT ' . $dummy . ".name||'_'||" . $dummy . ".grouping||'_'||" . $dummy . ".id) as {$alias}"; case 'postgresql': return 'string_agg(DISTINCT ' . $dummy . ".name||'_'||" . $dummy . ".grouping||'_'||" . $dummy . ".id) as {$alias}"; } }
/** * Get platform specific group_concat token for provided column * * @param string $column * @param string $alias * @param QueryBuilder $query * * @return string */ protected function getPlatformGroupConcat($column, $alias, QueryBuilder $query) { $platform = $query->getConnection()->getDatabasePlatform()->getName(); switch ($platform) { case 'mysql': return "GROUP_CONCAT(DISTINCT {$column}) as {$alias}"; case 'sqlite': return "GROUP_CONCAT(DISTINCT {$column}) as {$alias}"; case 'postgresql': return "string_agg(distinct {$column}, ',') as {$alias}"; } }
/** * @param array $request * @return array */ public function make(array $request) { $output = ['data' => [], 'draw' => $request['draw'], 'recordsFiltered' => 0, 'recordsTotal' => 0]; /** * Order By */ if (isset($request['order'])) { for ($i = 0; $i < count($request['order']); ++$i) { $j = intval($request['order'][$i]['column']); if ($request['columns'][$j]['orderable'] != 'true') { continue; } $column = $request['columns'][$j]['data']; $sort = $request['order'][$i]['dir']; $this->query->addOrderBy($column, $sort); } } /** * Count All */ $temp = clone $this->query; $temp->resetQueryPart('select'); $temp->resetQueryPart('orderBy'); $temp->select("COUNT(*)"); $output['recordsTotal'] = $temp->execute()->fetchColumn(0); /** * Filter */ for ($i = 0; $i < count($request['columns']); ++$i) { if ($request['columns'][$i]['searchable'] != 'true') { continue; } $value = $request['columns'][$i]['search']['value']; if (strlen($value) > 0) { $column = $request['columns'][$i]['data']; $value = $this->query->getConnection()->quote("{$value}%"); $this->query->andHaving($this->query->expr()->like($column, $value)); } } /** * Search */ if (isset($request['search'])) { $value = $request['search']['value']; if (strlen($value) > 0) { for ($i = 0; $i < count($request['columns']); ++$i) { if ($request['columns'][$i]['searchable'] != 'true') { continue; } $column = $request['columns'][$i]['data']; $this->query->orHaving($this->query->expr()->like($column, ':search')); } $this->query->setParameter('search', "%{$value}%"); } } /** * Count Filtered */ $temp = clone $this->query; $temp->resetQueryPart('orderBy'); $output['recordsFiltered'] = $temp->execute()->rowCount(); /** * Limit */ if (isset($request['start'])) { $this->query->setFirstResult($request['start']); } if (isset($request['length'])) { $this->query->setMaxResults($request['length']); } /** * Fetch Results */ $output['data'] = $this->query->execute()->fetchAll(\PDO::FETCH_ASSOC); /** * Add Filter */ return $output; }
public function testGetConnection() { $qb = new QueryBuilder($this->conn); $this->assertSame($this->conn, $qb->getConnection()); }
/** * Get platform specific group_concat token for provided column * * @param string $alias * @param QueryBuilder $query * * @return string */ protected function getPlatformGroupConcat($alias, QueryBuilder $query) { $platform = $query->getConnection()->getDatabasePlatform()->getName(); switch ($platform) { case 'mysql': return "GROUP_CONCAT(DISTINCT CONCAT_WS('_', f.name, f.grouping, f.id)) as {$alias}"; case 'sqlite': return "GROUP_CONCAT(DISTINCT f.name||'_'||f.grouping||'_'||f.id) as {$alias}"; case 'postgresql': return "string_agg(DISTINCT f.name||'_'||f.grouping||'_'||f.id) as {$alias}"; } }
/** * Gets the associated DBAL Connection for this query builder. * * @return \Doctrine\DBAL\Connection */ public function getConnection() { return $this->qb->getConnection(); }
/** * Get platform specific group_concat token for provided column. * * @param string $column * @param string $order * @param string $alias * @param QueryBuilder $query * * @return string */ protected function getPlatformGroupConcat($column, $order, $alias, QueryBuilder $query) { $platform = $query->getConnection()->getDatabasePlatform()->getName(); switch ($platform) { case 'mysql': return "GROUP_CONCAT({$column} ORDER BY {$order} ASC) as {$alias}"; case 'sqlite': return "GROUP_CONCAT({$column}) as {$alias}"; case 'postgresql': return "string_agg({$column}" . "::character varying, ',' ORDER BY {$order}) as {$alias}"; } }
/** * @param QueryBuilder $queryBuilder */ public function __construct(QueryBuilder $queryBuilder) { $this->connection = $queryBuilder->getConnection(); $this->queryBuilder = $queryBuilder; }
/** * @TODO Temporary workaround for PostgreSQL databases that don't use a sequence. * * @param QueryBuilder $query */ private function setLastInsertId(QueryBuilder $query) { $seq = null; if ($query->getConnection()->getDatabasePlatform()->getName() === 'postgresql') { $sequences = $query->getConnection()->getSchemaManager()->listSequences(); $desiredSeq = $query->getQueryPart('from')['table'] . '_id_seq'; foreach ($sequences as $sequence) { if ($desiredSeq === $sequence->getName()) { $seq = $desiredSeq; break; } } } $this->lastInsertId = $query->getConnection()->lastInsertId($seq); }
/** * @param QueryBuilder $builder * @param string $class * @param int $limit * @param int $offset * * @return array */ protected function fetchCollection(QueryBuilder $builder, $class, $limit, $offset) { $statement = $builder->execute(); $statement->setFetchMode(PDOConnection::FETCH_ASSOC); $platform = $builder->getConnection()->getDatabasePlatform(); $types = $this->getModelSchemes()->getAttributeTypeInstances($class); $models = []; while (($attributes = $statement->fetch()) !== false) { $models[] = $this->readInstanceFromAssoc($class, $attributes, $types, $platform); } return $this->normalizePagingParams($models, $limit, $offset); }