andWhere() public method

$qb = $conn->createQueryBuilder() ->select('u') ->from('users', 'u') ->where('u.username LIKE ?') ->andWhere('u.is_active = 1');
See also: where()
public andWhere ( mixed $where )
$where mixed The query restrictions.
Exemplo n.º 1
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;
 }
Exemplo n.º 2
0
 /**
  * {@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;
 }
Exemplo n.º 3
0
 /**
  * @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))));
     }
 }
Exemplo n.º 4
0
 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;
 }
Exemplo n.º 5
0
 /**
  * {@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;
     }
 }
Exemplo n.º 6
0
 /**
  * {@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;
 }
Exemplo n.º 10
0
 /**
  * @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);
             }
         }
     }
 }
Exemplo n.º 11
0
 /**
  * {@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()]));
 }
Exemplo n.º 12
0
 /**
  * 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);
         }
     }
 }
Exemplo n.º 13
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('drName', 'ASC');
             break;
     }
     if ($this->search) {
         $query->andWhere('drName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
     }
     $query->andWhere('drDeleted is NULL');
     return $query;
 }
Exemplo n.º 14
0
 public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
 {
     if (!$this->includeAllGroups) {
         $query->andWhere('g.gID > :minGroupID');
         $query->setParameter('minGroupID', REGISTERED_GROUP_ID);
     }
     return $query;
 }
Exemplo n.º 15
0
 /**
  * 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;
 }
Exemplo n.º 16
0
 /**
  * @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;
 }
Exemplo n.º 18
0
 /**
  * {@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));
 }
Exemplo n.º 19
0
 /**
  * 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));
 }
Exemplo n.º 20
0
 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;
 }
Exemplo n.º 22
0
 /**
  * @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;
 }
Exemplo n.º 23
0
 /**
  * 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;
 }
Exemplo n.º 24
0
 /**
  * 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;
 }
Exemplo n.º 25
0
 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);
 }
Exemplo n.º 26
0
 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'];
     }
 }
Exemplo n.º 27
0
 /**
  * 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;
 }
Exemplo n.º 28
0
 /**
  * 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);
         }
     }
 }
Exemplo n.º 29
0
 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);
 }