$qb = $conn->createQueryBuilder() ->select('u') ->from('users', 'u') ->where('u.username LIKE ?') ->andWhere('u.is_active = 1');
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 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 finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query) { if (!$this->includeInactiveUsers) { $query->andWhere('u.uIsActive = :uIsActive'); $query->setParameter('uIsActive', true); } if (!$this->includeUnvalidatedUsers) { $query->andWhere('u.uIsValidated != 0'); } 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; } }
/** * {@inheritdoc} */ public function buildQuery(QueryBuilder $queryBuilder, array $values = array()) { if (!array_key_exists('salary', $values)) { return; } //Switch min and max salary if user has inverted the fields if (isset($values['salary']['min']) && isset($values['salary']['max']) && $values['salary']['max'] < $values['salary']['min']) { list($values['salary']['min'], $values['salary']['max']) = [$values['salary']['max'], $values['salary']['min']]; } if (isset($values['salary']['min'])) { $queryBuilder->andWhere('response.grossAnnualSalary >= :minSalary')->setParameter('minSalary', $values['salary']['min']); } if (isset($values['salary']['max'])) { $queryBuilder->andWhere('response.grossAnnualSalary <= :maxSalary')->setParameter('maxSalary', $values['salary']['max']); } }
/** * @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); } }
public function match(QueryBuilder $qb) { if ($this->has('username')) { $qb->andWhere('u.username = :username'); $qb->setParameter('username', $this->get('username')); } }
public function where($column, $operator = null, $value = null, $boolean = 'and') { $args = func_get_args(); if (count($args) == 1) { parent::where($args[0]); return $this; } $column = $args[0]; $boolean = 'and'; $operator = '='; if (count($args) == 2) { $value = $args[1]; } if (count($args) == 3) { $operator = $args[1]; $value = $args[2]; } if (count($args) == 4) { $operator = $args[1]; $value = $args[2]; $boolean = $args[3]; } if (is_array($value)) { $operator = $operator == '=' ? 'in' : 'notIn'; $where_clause = $this->expr()->{$operator}($column, parent::createNamedParameter($value, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY)); } else { $where_clause = $column . $operator . parent::createNamedParameter($value); } if ($boolean == 'and') { parent::andWhere($where_clause); } elseif ($boolean == 'or') { parent::orWhere($where_clause); } return $this; }
/** * @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); } } } }
/** * {@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()])); }
/** * Builds the raw query * * @return void */ protected function buildQuery() { $this->queryBuilder = $this->connection->createQueryBuilder(); $this->queryBuilder->select((array) $this->conf->select); /* * Main table */ $this->queryBuilder->from($this->conf->from->name, $this->conf->from->alias); /* * Inner join, right join, left join */ $joinTypes = ['innerJoin', 'leftJoin', 'rightJoin']; foreach ($joinTypes as $joinType) { if (isset($this->conf->{$joinType})) { $joins = $this->conf->{$joinType}; $this->buildJoins($joinType, $joins); } } /* * Condition */ if (isset($this->conf->where)) { foreach ($this->conf->where as $where) { $this->queryBuilder->andWhere($where); } } }
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('drName', 'ASC'); break; } if ($this->search) { $query->andWhere('drName like ?')->setParameter($paramcount++, '%' . $this->search . '%'); } $query->andWhere('drDeleted is NULL'); return $query; }
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query) { if (!$this->includeAllGroups) { $query->andWhere('g.gID > :minGroupID'); $query->setParameter('minGroupID', REGISTERED_GROUP_ID); } return $query; }
/** * Parses the `CriteriaExpression` and appropriately configures the passed query builder. * * @param QueryBuilder $queryBuilder Query builder to be configured. * @param CriteriaExpression|null $criteria Criteria to be parsed. * * @return QueryBuilder */ public function parse(QueryBuilder $queryBuilder, CriteriaExpression $criteria = null) { if (!$criteria || count($criteria->getCriteria()) === 0) { return $queryBuilder; } $queryBuilder->andWhere($this->parseCriteria($queryBuilder, $criteria)); return $queryBuilder; }
/** * @inheritdoc */ public function __call($name, $arguments) { $operator = $this->operator($name); if (!$operator) { throw new \BadMethodCallException('Call to undefined method ' . get_class($this) . '::' . $name . '()'); } $condition = $operator($this, ...$arguments); $this->queryBuilder->andWhere($condition); return $this; }
/** * 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; }
/** * {@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)); }
/** * Build the query with active filters * * @param QueryBuilder $queryBuilder * @param array $values */ public function buildQuery(QueryBuilder $queryBuilder, array $values = []) { if (!array_key_exists($this->getName(), $values) || 0 === count($values[$this->getName()])) { return; } $condition = array_map(function ($value) use($queryBuilder) { if ($value === '') { return $queryBuilder->expr()->isNull('response.gender'); } return $queryBuilder->expr()->eq('response.gender', $value); }, $values[$this->getName()]); $queryBuilder->andWhere(new CompositeExpression(CompositeExpression::TYPE_OR, $condition)); }
private function addWhereIn($where) { $conditionName = $this->getConditionName($where); if (empty($this->conditions[$conditionName]) or !is_array($this->conditions[$conditionName])) { return $this; } $marks = array(); foreach (array_values($this->conditions[$conditionName]) as $index => $value) { $marks[] = ":{$conditionName}_{$index}"; $this->conditions["{$conditionName}_{$index}"] = $value; } $where = str_replace(":{$conditionName}", implode(',', $marks), $where); return parent::andWhere($where); }
/** * @param QueryBuilder $qb * @param string $field * @param ArrayValuesContains $value * * @return array */ public function applyWhereCriteria(QueryBuilder $qb, $field, $value) { $parametersToSet = []; // hack to reduce count of fetched records // TODO - hack for NotEmpty foreach ($value->getValues() as $criterionValue) { if (!is_string($criterionValue)) { continue; } $qb->andWhere($qb->expr()->like($field, '?')); $parametersToSet[] = '%' . $criterionValue . '%'; } return $parametersToSet; }
/** * @param QueryBuilder $qb * @param $fields * @param Like $value * * @return null|array parameters to set to DBAL */ public function applyWhereCriteria(QueryBuilder $qb, $fields, $value) { $likeExpressions = []; $parameters = []; $searchValue = $value->getValue(); // for this criterion we handle multiple fields foreach (explode('|', $fields) as $field) { $likeExpressions[] = $qb->expr()->like($field, '?'); $parameters[] = "%{$searchValue}%"; } $whereExpr = call_user_func_array([$qb->expr(), 'orX'], $likeExpressions); $qb->andWhere($whereExpr); return $parameters; }
/** * Filter field with `LIKE`. * * @param string $filterName * @param string $field */ protected function likeFilter($filterName, $field) { $info = $this->extract($filterName); $info['values'] = explode(',', str_replace('*', '%', $info['values'])); foreach ($info['values'] as $value) { if ($info['cond']) { $expr = $this->expr->like($field, "'%{$value}%'"); } else { $expr = $this->expr->notLike($field, "'%{$value}%'"); } } $this->builder->andWhere($expr); $this->filters[$filterName] = $info; }
/** * Add the additional query conditions returned by the QueryRestrictionBuilder * to the current query and return the original set of conditions so that they * can be restored after the query has been built/executed. * * @return \Doctrine\DBAL\Query\Expression\CompositeExpression|mixed */ protected function addAdditonalWhereConditions() { $queryRestrictionBuilder = GeneralUtility::makeInstance(QueryRestrictionBuilder::class, $this->getQueriedTables(), $this->expr(), $this->getQueryContext()); $originalWhereConditions = $this->concreteQueryBuilder->getQueryPart('where'); if ($originalWhereConditions instanceof CompositeExpression) { $originalWhereConditions = clone $originalWhereConditions; } $additionalQueryRestrictions = $queryRestrictionBuilder->getVisibilityConstraints(); if ($additionalQueryRestrictions->count() !== 0) { // save the original query conditions so we can restore // them after the query has been built. $this->concreteQueryBuilder->andWhere($additionalQueryRestrictions); } return $originalWhereConditions; }
protected function addWhereIn($where) { $conditionName = $this->getConditionName($where); if (empty($this->conditions[$conditionName]) || !is_array($this->conditions[$conditionName])) { return $this; } $this->conditions[$conditionName] = array_unique($this->conditions[$conditionName]); $marks = array(); foreach (array_values($this->conditions[$conditionName]) as $index => $value) { $marks[] = ":{$conditionName}_{$index}"; $this->conditions["{$conditionName}_{$index}"] = $value; } $where = str_replace(":{$conditionName}", join(',', $marks), $where); return parent::andWhere($where); }
protected function processWhereCondition(array $condition, QBuilder $qbuilder) { $query = $condition['condition']; if (stripos($query, '?') === false) { $query .= " = ?"; } $type = isset($condition['type']) ? strtoupper($condition['type']) : 'AND'; if ($type == 'AND') { $qbuilder->andWhere($query); } else { $qbuilder->orWhere($query); } if (isset($condition['value'])) { $this->params[] = $condition['value']; } }
/** * Add the additional query conditions returned by the QueryRestrictionBuilder * to the current query and return the original set of conditions so that they * can be restored after the query has been built/executed. * * @return \Doctrine\DBAL\Query\Expression\CompositeExpression|mixed */ protected function addAdditionalWhereConditions() { $originalWhereConditions = $this->concreteQueryBuilder->getQueryPart('where'); $expression = $this->restrictionContainer->buildExpression($this->getQueriedTables(), $this->expr()); // This check would be obsolete, as the composite expression would not add empty expressions anyway // But we keep it here to only clone the previous state, in case we really will change it. // Once we remove this state preserving functionality, we can remove the count check here // and just add the expression to the query builder. if ($expression->count() > 0) { if ($originalWhereConditions instanceof CompositeExpression) { // Save the original query conditions so we can restore // them after the query has been built. $originalWhereConditions = clone $originalWhereConditions; } $this->concreteQueryBuilder->andWhere($expression); } // @todo add hook to be able to add additional restrictions return $originalWhereConditions; }
/** * Add required WHERE parameters. * * @param QueryBuilder $qb */ protected function addWhereActivity(QueryBuilder $qb, $options) { if (empty($options)) { return; } foreach ($options as $columnName => $option) { if (is_array($options[$columnName])) { $qb->andWhere($this->buildWhereOr($qb, $columnName, $option)); } elseif (!empty($options[$columnName])) { $qb->andWhere("{$columnName} = :{$columnName}")->setParameter($columnName, $option); } } }
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query) { if ($this->includeAliases) { $query->from('Pages', 'p')->leftJoin('p', 'Pages', 'pa', 'p.cPointerID = pa.cID')->leftJoin('p', 'PagePaths', 'pp', 'p.cID = pp.cID and pp.ppIsCanonical = true')->leftJoin('pa', 'PageSearchIndex', 'psi', 'psi.cID = if(pa.cID is null, p.cID, pa.cID)')->leftJoin('p', 'PageTypes', 'pt', 'pt.ptID = if(pa.cID is null, p.ptID, pa.ptID)')->leftJoin('p', 'CollectionSearchIndexAttributes', 'csi', 'csi.cID = if(pa.cID is null, p.cID, pa.cID)')->innerJoin('p', 'CollectionVersions', 'cv', 'cv.cID = if(pa.cID is null, p.cID, pa.cID)')->innerJoin('p', 'Collections', 'c', 'p.cID = c.cID')->andWhere('p.cIsTemplate = 0 or pa.cIsTemplate = 0'); } else { $query->from('Pages', 'p')->leftJoin('p', 'PagePaths', 'pp', '(p.cID = pp.cID and pp.ppIsCanonical = true)')->leftJoin('p', 'PageSearchIndex', 'psi', 'p.cID = psi.cID')->leftJoin('p', 'PageTypes', 'pt', 'p.ptID = pt.ptID')->leftJoin('c', 'CollectionSearchIndexAttributes', 'csi', 'c.cID = csi.cID')->innerJoin('p', 'Collections', 'c', 'p.cID = c.cID')->innerJoin('p', 'CollectionVersions', 'cv', 'p.cID = cv.cID')->andWhere('p.cPointerID < 1')->andWhere('p.cIsTemplate = 0'); } if ($this->pageVersionToRetrieve == self::PAGE_VERSION_RECENT) { $query->andWhere('cvID = (select max(cvID) from CollectionVersions where cID = cv.cID)'); } else { $query->andWhere('cvIsApproved = 1'); } if ($this->isFulltextSearch) { $query->addSelect('match(psi.cName, psi.cDescription, psi.content) against (:fulltext) as cIndexScore'); } if (!$this->includeInactivePages) { $query->andWhere('p.cIsActive = :cIsActive'); $query->setParameter('cIsActive', true); } if (!$this->includeSystemPages) { $query->andWhere('p.cIsSystemPage = :cIsSystemPage'); $query->setParameter('cIsSystemPage', false); } return $query; }
private function addBetweenFilter(QueryBuilder $query) { $query->andWhere($query->expr()->gte('main.age', ':age_min'))->andWhere($query->expr()->lt('main.age', ':age_max'))->setParameter('age_min', 30)->setParameter('age_max', 50); }