/**
* @return array
*/
public function fetch()
{
$data = $this->query->execute()->fetchAll(\PDO::FETCH_KEY_PAIR);
$keys = array_keys($data);
$this->query->setParameter(':lastId', array_pop($keys));
return array_values($data);
}
/**
* @param int $page Starts at 1
* @param bool $allResults
* @return DataObjectInterface[]
*/
public function getResults($page, $allResults = false)
{
if ($page < 1 || $page > $this->getPages()) {
throw new InvalidArgumentException("Page must be between 1 and {$this->getPages()}");
}
if (!$allResults) {
$this->page = $page;
$this->prev = $page > 1 ? $page - 1 : 0;
$this->next = $page < $this->pages ? $page + 1 : 0;
$this->qb->setMaxResults($this->pageSize)->setFirstResult(($page - 1) * $this->pageSize);
}
$statement = $this->qb->execute();
return $statement->fetchAll(\PDO::FETCH_CLASS, $this->class, $this->dependencies);
}
public function execute()
{
foreach ($this->conditions as $field => $value) {
$this->setParameter(":{$field}", $value);
}
return parent::execute();
}
public function setUp()
{
$builder = new QueryBuilder(Connection::get());
$builder->select('*')->from('test_users');
$this->data = $builder->execute()->fetchAll(\PDO::FETCH_OBJ);
$this->operations = new OperationCollection();
$this->service = new DoctrineProcessingService(new DoctrineProcessorResolver(), $this->operations, $builder);
$this->totalCount = (new QueryBuilder(Connection::get()))->select('count(*)')->from('test_users')->execute()->fetchColumn();
self::assertTrue($this->totalCount > 0);
}
/**
* @param QueryBuilder $query
* @return array
*/
protected function executeSelectQuery(QueryBuilder $query)
{
$rows = array();
$statement = $query->execute();
/** @todo Should configure which fetch method to use (fetch row by row or fetchAll) */
while (($row = $statement->fetch(PDO::FETCH_ASSOC)) !== false) {
$this->processRow($row, $rows);
}
$rows = $this->processRows($rows);
return $this->denormalizeRows($rows);
}
/**
* Prepare listing instance from Doctrine DBAL QueryBuilder.
*
* Total rows count for Listing instance must be set separately.
*
* @param DBALQueryBuilder
* @param boolean
* @param boolean
* @return self
*/
public function setDBALQuery(DBALQueryBuilder $query, $appendLimit = true, $appendOrder = true)
{
if ($appendLimit) {
$query->setFirstResult($this->offset())->setMaxResults($this->limit());
}
if ($appendOrder && $this->order()) {
call_user_func_array([$query, 'orderBy'], (array) $this->order());
}
$this->setItems($query->execute()->fetchAll());
return $this;
}
/**
* @return array
*/
protected function run()
{
if (!$this->result || $this->queryBuilder->getState() === QueryBuilder::STATE_DIRTY) {
$statement = $this->queryBuilder->execute();
$statement->setFetchMode(\PDO::FETCH_ASSOC);
$rows = $statement->fetchAll();
$statement->closeCursor();
$this->result = $this->mapper ? $this->mapper->collection($rows) : $rows;
}
return $this->result;
}
protected function getFilteredPostsIds(array $identifiers, $mask, array $orX = [])
{
$qb = new QueryBuilder($this->connection);
$qb->select('p.id')->from('posts', 'p');
$aclFilter = new DoctrineDbalAclFilter($qb);
$aclFilter->setAclSchema($this->aclSchema);
$aclFilter->apply('p', 'id', 'post-', $identifiers, $mask, $orX);
$result = array_map(function ($post) {
return (int) $post['id'];
}, $qb->execute()->fetchAll());
return [$result];
}
/**
* Executes this query using the bound parameters and their types.
*
* @return \Doctrine\DBAL\Driver\Statement|int
*/
public function execute()
{
if ($this->getType() !== \Doctrine\DBAL\Query\QueryBuilder::SELECT) {
return $this->concreteQueryBuilder->execute();
}
// Set additional query restrictions
$originalWhereConditions = $this->addAdditionalWhereConditions();
$result = $this->concreteQueryBuilder->execute();
// Restore the original query conditions in case the user keeps
// on modifying the state.
$this->concreteQueryBuilder->add('where', $originalWhereConditions, false);
return $result;
}
/**
* Load roles at RBAC creation.
*
* @param Event $e
* @return array
*/
public function loadRoles(Event $e)
{
$builder = new QueryBuilder($this->connection);
$options = $this->options;
$builder->select("role.{$options->getNameColumn()} AS name, parent.{$options->getNameColumn()} AS parent")->from($options->getTable(), 'role')->leftJoin('role', $options->getTable(), 'parent', "role.{$options->getJoinColumn()} = parent.{$options->getIdColumn()}");
$result = $builder->execute();
$roles = array();
foreach ($result as $row) {
$parentName = isset($row['parent']) ? $row['parent'] : 0;
unset($row['parent']);
$roles[$parentName][] = $row['name'];
}
$this->recursiveRoles($e->getRbac(), $roles);
}
/**
* Rewind
*/
public function rewind()
{
//Only perform rewind if current item is not the first element
if ($this->currentKey !== 0) {
$this->batchPosition = 0;
$this->queryBuilder->setFirstResult(0);
$this->queryBuilder->setMaxResults($this->batchSize);
/* @var $stmt \Doctrine\DBAL\Statement */
$stmt = $this->queryBuilder->execute();
$stmt->setFetchMode(\PDO::FETCH_ASSOC);
$this->currentItem = null;
$this->currentKey = -1;
$this->statement = $stmt;
$this->next();
}
}
/**
* Get list of referers ordered by it's count
*
* @param \Doctrine\DBAL\Query\QueryBuilder $query
* @param int $limit
* @param int $offset
*
* @return array
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getReferers($query, $limit = 10, $offset = 0)
{
$query->select('h.referer, count(h.referer) as sessions')->groupBy('h.referer')->orderBy('sessions', 'DESC')->setMaxResults($limit)->setFirstResult($offset);
return $query->execute()->fetchAll();
}
/**
* Get list of referers ordered by it's count
*
* @param \Doctrine\DBAL\Query\QueryBuilder $query
* @param int $limit
* @param int $offset
* @param string $column
* @param string $as
*
* @return array
* @throws \Doctrine\ORM\NoResultException
* @throws \Doctrine\ORM\NonUniqueResultException
*/
public function getMostVisited($query, $limit = 10, $offset = 0, $column = 'p.hits', $as = '')
{
if ($as) {
$as = ' as "' . $as . '"';
}
$query->select('p.title, p.id, ' . $column . $as)->groupBy('p.id, p.title, ' . $column)->orderBy($column, 'DESC')->setMaxResults($limit)->setFirstResult($offset);
return $query->execute()->fetchAll();
}
/**
* @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;
}
/**
* Internal method to hydrate and return a single QueryBuilder result
*
* @return Entity | false
**/
protected function findOneWith(QueryBuilder $query)
{
$result = $query->execute()->fetch();
if ($result) {
return $this->hydrate($result, $query);
} else {
return false;
}
}
/**
* @param QueryBuilder $data
* @return Traversable
*/
protected function afterOperations($data)
{
return new ArrayIterator($data->execute()->fetchAll(PDO::FETCH_OBJ));
}
/**
* 加载数据库结果集
*
* @param bool $multiple 是否加载多行数据
* @return $this|mixed
*/
protected function _loadResult($multiple = false)
{
$this->_dbBuilder->from($this->db()->quoteIdentifier($this->_tableName), $this->db()->quoteIdentifier($this->_objectName));
// 只获取单条记录
if (false === $multiple) {
$this->_dbBuilder->setMaxResults(1);
}
// 默认选择所有字段
$this->_dbBuilder->addSelect($this->_buildSelect());
// 处理排序问题
if (!isset($this->_dbApplied['orderBy']) && !empty($this->_sorting)) {
foreach ($this->_sorting as $column => $direction) {
if (false === strpos($column, '.')) {
// Sorting column for use in JOINs
$column = $this->_objectName . '.' . $column;
}
$this->_dbBuilder->orderBy($column, $direction);
}
}
if (true === $multiple) {
$result = $this->_dbBuilder->execute();
$result->setFetchMode(PDO::FETCH_CLASS, $this->_loadMultiResultFetcherClass(), $this->_loadMultiResultFetcherConstructor());
$this->reset();
return $result->fetchAll();
} else {
$result = $this->_dbBuilder->execute()->fetch();
$this->reset();
if ($result) {
$this->_loadValues($result);
} else {
$this->clear();
}
return $this;
}
}
/**
* Calculates the search tolerance and adds an where condition
* to the query.
*
* @param QueryBuilder $query
*/
private function addToleranceCondition(QueryBuilder $query)
{
$query->select("MAX(" . $this->getRelevanceSelection() . ")");
/**@var $statement \Doctrine\DBAL\Driver\ResultStatement */
$statement = $query->execute();
$highestRanking = $statement->fetch(\PDO::FETCH_COLUMN);
//calculates the tolerance limit
$distance = $this->config->get('fuzzySearchMinDistancenTop', 20);
if ($highestRanking && $distance) {
$toleranceLimit = $highestRanking / 100 * $distance;
$query->andWhere('(' . $this->getRelevanceSelection() . ') > ' . $toleranceLimit);
}
}
/**
* Loads Location data for the given $pass.
*
* @param \Doctrine\DBAL\Query\QueryBuilder $queryBuilder
* @param int $pass
*
* @return array
*/
protected function loadLocationData(QueryBuilder $queryBuilder, $pass)
{
$queryBuilder->setFirstResult($pass * $this->bulkCount);
$queryBuilder->setMaxResults($this->bulkCount);
$statement = $queryBuilder->execute();
$rows = $statement->fetchAll(PDO::FETCH_ASSOC);
return $rows;
}
public function expedierCommande($id)
{
$queryBuilder = new QueryBuilder($this->db);
$queryBuilder->update('commandes', 'c')->set('c.id_etat', ':etat')->set('c.date_expedition', ':date_expedition')->where('c.id = :id')->setParameter(':etat', 1)->setParameter(':date_expedition', date('Y-m-d'))->setParameter(':id', $id);
return $queryBuilder->execute();
}
/**
* @param DBAL\Query\QueryBuilder $qb
* @return array
*/
public function findByQueryBuilder(DBAL\Query\QueryBuilder $qb)
{
self::$QB = $qb;
return $qb->execute()->fetchAll(\PDO::FETCH_CLASS, get_class($this), [null, true]);
}
/**
* @param QueryBuilder $queryBuilder
* @return array|null
*/
public function findByQueryBuilder(QueryBuilder $queryBuilder)
{
$queryResult = $queryBuilder->execute()->fetchAll();
if (!$queryResult) {
return null;
}
$result = [];
$entity = $this->em->getObject($this->className);
foreach ($queryResult as $row) {
$result[] = $this->hydrate(clone $entity, $row);
}
return $result;
}
/**
* @param QueryBuilder $qb
* @return DataObjectInterface[]
*/
protected function fetchAll(QueryBuilder $qb)
{
/** @var Statement $statement */
$statement = $qb->execute();
$objects = $statement->fetchAll(\PDO::FETCH_CLASS, $this->getClassName(), $this->objectDependencies);
foreach ($objects as $object) {
$this->dispatchEvents('loaded', $object);
}
return $objects;
}
public function get(array $columns = ['*'])
{
$columns = implode(', ', $columns);
$this->select($columns);
return parent::execute()->fetchAll(\PDO::FETCH_CLASS, get_class($this->model), [$this->model->app(), true]);
}
public function getLogs()
{
$queryBuilder = new QueryBuilder($this->db);
$queryBuilder->select('*')->from('logs', 'l')->where('l.user_id = :id_user')->setParameter(':id_user', $this->id_user)->addOrderBy('l.id', 'ASC');
return $queryBuilder->execute()->fetchAll();
}
/**
* Fetch the count integet from a query
*
* @param QueryBuilder $query
*
* @return integer
*/
public function fetchCount(QueryBuilder $query)
{
$data = $query->execute()->fetch();
return (int) $data['count'];
}
/**
* @param DBALQueryBuilder|Query $queryBuilder
*
* @return int[]
*/
private function getPostIds($queryBuilder)
{
$ids = [];
if ($queryBuilder instanceof DBALQueryBuilder) {
foreach ($queryBuilder->execute()->fetchAll() as $post) {
$ids[] = (int) $post['id'];
}
} elseif ($queryBuilder instanceof Query) {
foreach ($queryBuilder->getResult() as $post) {
$ids[] = $post->getId();
}
}
return array_unique($ids);
}
/**
* Load the requested resources into RBAC.
*
* @param Rbac $rbac
* @param string $role
* @param string|null $permission
* @return \Doctrine\DBAL\Query\QueryBuilder
*/
protected function load($rbac, $role, $permission = null)
{
$options = $this->options;
$builder = new QueryBuilder($this->connection);
// Role always present
$builder->select('node.name')->from($options->getRoleTable(), 'node')->from($options->getRoleTable(), 'parent')->where('node.lft BETWEEN parent.lft AND parent.rgt')->andWhere('parent.name = :role')->orderBy('node.lft');
$builder->setParameter('role', $role);
// Permission optional
if ($permission) {
$builder->addSelect('permission.name AS permission')->leftJoin('node', 'role_permission', 'rp', 'node.id = rp.role_id')->leftJoin('node', 'permission', 'permission', 'rp.permission_id = permission.id')->andWhere('(permission.name = :permission OR permission.name IS NULL)');
$builder->setParameter('permission', $permission);
}
$parent = null;
foreach ($builder->execute() as $row) {
if ($parent) {
if (!$rbac->hasRole($row['name'])) {
$rbac->getRole($parent)->addChild($row['name']);
}
} elseif (!$rbac->hasRole($row['name'])) {
$rbac->addRole($row['name']);
}
if ($permission) {
if ($row['permission']) {
$rbac->getRole($row['name'])->addPermission($row['permission']);
}
}
$parent = $row['name'];
}
return $builder;
}
protected function _doExecute()
{
return $this->qb->execute();
}
/**
* Executes this query using the bound parameters and their types.
*
* Uses {@see Connection::executeQuery} for select statements and {@see Connection::executeUpdate}
* for insert, update and delete statements.
*
* @return \Doctrine\DBAL\Driver\Statement|int
*/
public function execute()
{
return $this->queryBuilder->execute();
}
