/** * @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); }
/** * @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); }
/** * {@inheritdoc} */ public function buildQuery(QueryBuilder $queryBuilder, array $values = array()) { if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) { return; } $queryBuilder->andWhere($queryBuilder->expr()->in('response.companyType', $values[$this->getName()])); }
protected function collection() { if (!$this->result) { $this->result = $this->queryBuilder->getQuery()->getResult(); } return $this->result; }
/** * @param QueryBuilder $qb * @param callable|null $countQueryBuilderModifier */ public function __construct(QueryBuilder $qb, callable $countQueryBuilderModifier = null) { $this->qb = $qb; $this->countQueryBuilderModifier = $countQueryBuilderModifier ?: function (QueryBuilder $qb) { return $qb->select('COUNT(*)'); }; }
/** * @param $tableName * @param $conditions * * @return array * * @throws LookupError * * @since 1.1.0 * * @author Eddilbert Macharia (http://eddmash.com) <*****@*****.**> */ public static function filters(QueryBuilder $queryBuilder, $conditions) { // default lookup is equal $lookup = 'eq'; // we add the or conditions afterwards to avoid them being mistaken for "and" conditions when they come first $or_combine = []; $and_combine = []; // create where clause from the conditions given foreach ($conditions as $condition) { foreach ($condition as $key => $value) { $column = self::getLookupColumn($key); $lookup = self::getLookUP($key); $value = self::prepareValue($value, $lookup); echo self::$lookuOptions[$lookup] . '<br>'; echo $queryBuilder->createNamedParameter($value) . '<br>'; echo $value . '<br>'; $lookupCondition = sprintf(self::$lookuOptions[$lookup], $queryBuilder->createNamedParameter($value)); $queryString = sprintf('%s %s', $column, $lookupCondition); if (self::combine($key) === self::$or) { $queryBuilder->orWhere($queryString); } else { $queryBuilder->andWhere($queryString); } } } }
/** * @param QueryBuilder $builder */ public static function logBuilder($builder) { $sql = $builder->getSQL(); $params = $builder->getParameters(); self::log("Running Query: " . $sql . " with params:"); self::logObject($params); }
/** * Applies operation to data source and returns modified data source. * * @param QueryBuilder $src * @param OperationInterface|SortOperation $operation * @return QueryBuilder */ public function process($src, OperationInterface $operation) { $field = $operation->getField(); $order = $operation->getOrder(); $src->orderBy($field, $order); return $src; }
/** * @param QueryBuilder $builder * @param $column * @param $value * @throws Exception * @return string */ public function __invoke(QueryBuilder $builder, $column, $value) { if (!is_array($value)) { throw new Exception("Use of IN operator expects value to be array. Got " . gettype($value) . "."); } return $column . ' IN (' . $builder->createPositionalParameter($value, Connection::PARAM_STR_ARRAY) . ')'; }
public function match(QueryBuilder $qb) { if ($this->has('username')) { $qb->andWhere('u.username = :username'); $qb->setParameter('username', $this->get('username')); } }
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query) { $paramcount = 0; if (!empty($this->gIDs)) { $validgids = array(); foreach ($this->gIDs as $gID) { if ($gID > 0) { $validgids[] = $gID; } } if (!empty($validgids)) { $query->innerJoin('p', 'VividStoreProductGroups', 'g', 'p.pID = g.pID and g.gID in (' . implode(',', $validgids) . ')'); if (!$this->groupMatchAny) { $query->having('count(g.gID) = ' . count($validgids)); } } } switch ($this->sortBy) { case "alpha": $query->orderBy('pName', 'ASC'); break; case "date": $query->orderBy('pDateAdded', 'DESC'); break; case "popular": $pr = new StoreProductReport(); $pr->sortByPopularity(); $products = $pr->getProducts(); $pIDs = array(); foreach ($products as $product) { $pIDs[] = $product['pID']; } foreach ($pIDs as $pID) { $query->addOrderBy("pID = ?", 'DESC')->setParameter($paramcount++, $pID); } break; } switch ($this->featured) { case "featured": $query->andWhere("pFeatured = 1"); break; case "nonfeatured": $query->andWhere("pFeatured = 0"); break; } if (!$this->showOutOfStock) { $query->andWhere("pQty > 0 OR pQtyUnlim = 1"); } if ($this->activeOnly) { $query->andWhere("pActive = 1"); } if (is_array($this->cIDs) && !empty($this->cIDs)) { $query->innerJoin('p', 'VividStoreProductLocations', 'l', 'p.pID = l.pID and l.cID in (' . implode(',', $this->cIDs) . ')'); } $query->groupBy('p.pID'); if ($this->search) { $query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%'); } return $query; }
/** * @dataProvider beforeGetListHarness */ public function testBeforeGetList($query, $expectedQuery, $expectedParams) { $this->queryBuilder->select('o.*')->from('posts', 'o'); $request = new Request($query); $queryBuilder = $this->postDecorator->beforeGetList($this->queryBuilder, $request); $this->assertEquals($expectedQuery, (string) $queryBuilder); $this->assertEquals($expectedParams, $queryBuilder->getParameters()); }
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query) { if (!$this->includeAllGroups) { $query->andWhere('g.gID > :minGroupID'); $query->setParameter('minGroupID', REGISTERED_GROUP_ID); } return $query; }
public function testSetQueryBuilder02() { $contain = $this->testConstruct02(); $queryBuilder = new QueryBuilder($this->getConnection()); $queryBuilder->select('*')->from('table', 't'); $contain->setQueryBuilder($queryBuilder); return $contain; }
/** * @param QueryBuilder $queryBuilder * @param SortInterface $sort * @param Mapping $mapping */ public static function sort(QueryBuilder $queryBuilder, SortInterface $sort, Mapping $mapping) { $columns = $mapping->map(); foreach ($sort->orders() as $propertyName => $order) { self::guardColumnExists($columns, $propertyName); $queryBuilder->orderBy($columns[$propertyName], $order->isAscending() ? Order::ASCENDING : Order::DESCENDING); } }
/** * Add a query from a query builder * * @param QueryBuilder $qb * @return $this */ public function addQueryFromBuilder(QueryBuilder $qb) { /** * @TODO : How to manage query params ? */ $this->queries[] = new Query($qb->getSQL()); return $this; }
/** * @see \ComPHPPuebla\Doctrine\Query\Specification::match() */ public function match(QueryBuilder $qb) { if ($this->has('page')) { $this->page = $this->get('page'); $this->initPageSize(); $qb->setFirstResult($this->calculateOffset())->setMaxResults($this->pageSize); } }
/** * {@inheritdoc} */ public function apply($fromAlias, $fromIdentifier, $resourcePrefix, array $requesterIdentifiers, $mask, array $orX = []) { $this->queryBuilder->leftJoin($fromAlias, $this->getAclSchema()->getPermissionsTableName(), 'acl_p', 'acl_p.resource = ' . $this->connection->getDatabasePlatform()->getConcatExpression(':acl_prefix', $fromAlias . '.' . $fromIdentifier)); $orX[] = 'acl_p.requester IN (:acl_identifiers) AND :acl_mask = (acl_p.mask & :acl_mask)'; $this->queryBuilder->andWhere(implode(' OR ', $orX)); $this->queryBuilder->setParameter('acl_prefix', $resourcePrefix, \PDO::PARAM_STR)->setParameter('acl_identifiers', $requesterIdentifiers, Connection::PARAM_STR_ARRAY)->setParameter('acl_mask', $mask, \PDO::PARAM_INT); return $this->queryBuilder; }
/** * @deprecated */ public function filter($field, $value, $comparison = '=') { if ($field == false) { $this->query->andWhere($value); // ugh } else { $this->query->andWhere(implode(' ', array($field, $comparison, $this->query->createNamedParameter($value)))); } }
public function testItShouldWorksWithParametersInTheOrX() { $qb = new QueryBuilder($this->connection); $qb->select('p.id')->from('posts', 'p')->setParameter('status', 'odd'); $aclFilter = new DoctrineDbalAclFilter($qb); $aclFilter->setAclSchema($this->aclSchema); $aclFilter->apply('p', 'id', 'post-', ['user-1'], 1, ['status = :status']); $this->assertEquals(['status' => 'odd', 'acl_prefix' => 'post-', 'acl_identifiers' => ['user-1'], 'acl_mask' => 1], $qb->getParameters()); }
protected function getDataProvider() { if (!$this->provider) { $builder = new QueryBuilder(Connection::get()); $builder->select('*')->from('test_users'); $this->provider = new DoctrineDataProvider($builder); } return $this->provider; }
private function setAddValues(array $serialized, QueryBuilder $qb) { $i = 0; foreach ($serialized as $column => $value) { $qb->setValue($column, '?'); $qb->setParameter($i, $value); ++$i; } }
/** * Load user by username * * @param string $username * @return User * @throws \Exception */ public function loadUserByUsername($username) { $qb = new QueryBuilder($this->db); $stmt = $qb->select('username, password, roles')->from('users', 'u')->where('u.username = :username')->andWhere('u.active = 1')->setParameter('username', $username)->execute(); if (!($user = $stmt->fetchObject())) { throw new \Exception(sprintf('User "%s" not found.', $username)); } return new User($user->username, $user->password, explode(',', $user->roles), true, true, true, true); }
/** * {@inheritdoc} */ public function buildQuery(QueryBuilder $queryBuilder, array $values = []) { if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) { return; } $campaigns = array_map(function (Campaign $item) { return $item->getId(); }, $values[$this->getName()]->toArray()); $queryBuilder->andWhere($queryBuilder->expr()->in('response.campaign_id', $campaigns)); }
/** * Applies operation to data source and returns modified data source. * * @param QueryBuilder $src * @param OperationInterface|FilterOperation $operation * @return QueryBuilder */ public function process($src, OperationInterface $operation) { $value = $operation->getValue(); $operator = $operation->getOperator(); $fieldName = $operation->getField(); $parameterName = 'p' . md5($fieldName . $operator); $src->andWhere("{$fieldName} {$operator} :{$parameterName}"); $src->setParameter($parameterName, $value); return $src; }
/** * @param QueryBuilder $builder * @param $column * @param $value * @throws Exception * @return string */ public function __invoke(QueryBuilder $builder, $column, $value) { if (is_array($value) && !empty($value)) { return $column . ' NOT IN (' . $builder->createPositionalParameter($value, Connection::PARAM_STR_ARRAY) . ')'; } if ($value === null || is_array($value) && empty($value)) { return $column . ' IS NOT NULL'; } return $column . ' != ' . $builder->createPositionalParameter($value); }
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); }
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query) { $paramcount = 0; if (isset($this->gID) && $this->gID > 0) { $query->where('gID = ?')->setParameter($paramcount++, $this->gID); } switch ($this->sortBy) { case "alpha": $query->orderBy('pName', 'ASC'); break; case "date": $query->orderBy('pDateAdded', 'DESC'); break; } switch ($this->featured) { case "featured": $query->andWhere("pFeatured = 1"); break; case "nonfeatured": $query->andWhere("pFeatured = 0"); break; } if ($this->activeOnly) { $query->andWhere("pActive = 1"); } if ($this->search) { $query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%'); } return $query; }
/** * {@inheritdoc} */ public function restrict($expression, $condition = DataSourceInterface::CONDITION_AND) { switch ($condition) { case DataSourceInterface::CONDITION_AND: $this->queryBuilder->andWhere($expression); break; case DataSourceInterface::CONDITION_OR: $this->queryBuilder->orWhere($expression); break; } }
/** * @param QueryBuilder $query The query builder * * @return string */ private function getNextAlias($query) { if (!preg_match_all('~\\b(\\w+)\\.~', $query->getSQL(), $matches)) { return 'b'; } $aliases = array_unique($matches[1]); for ($alias = 'b'; in_array($alias, $aliases); $alias++) { continue; } return $alias; }