/**
* @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;
}
