public function setUp() { $this->queryConfig = new QueryConfig(); $this->queryConfig->setLimit(5)->setSelect(array('t.a', 't.b', 't.c'))->setPage(2)->setOrderBy(array('t.d' => true, 't.e' => false))->addParameter('query', '%xxx%')->getConstraints()->add('LOWER(t.a) LIKE :query')->add('LOWER(t.c) LIKE :query'); $this->queryBuilder = new QueryBuilderMock(); $this->queryBuilder->from('Table', 't'); if ($this->queryConfig->getConstraints()->count() > 0) { $this->queryBuilder->andWhere($this->queryConfig->getConstraints()); } if (count($this->queryConfig->getParameters()) > 0) { $this->queryBuilder->setParameters($this->queryConfig->getParameters()); } $this->dataProvider = new DataProvider(); }
public function manipulateQuery(QueryBuilder $query, $prefix = 'hv') { $query->where("{$prefix}.createdAt >= :startDate"); $query->andWhere("{$prefix}.createdAt <= :endDate"); $query->setParameters(['startDate' => $this->period->getStartDate(), 'endDate' => $this->period->getEndDate()]); return $query; }
/** * {@inheritdoc} */ public function setParameters($parameters) { $this->parameters += $parameters; if ($this->qb instanceof QueryBuilder) { $this->qb->setParameters($this->parameters); } return $this; }
/** * Sets a collection of query parameters for the query being constructed. * * <code> * $qb = $em->createQueryBuilder() * ->select('u') * ->from('User', 'u') * ->where('u.id = :user_id1 OR u.id = :user_id2') * ->setParameters(new ArrayCollection(array( * new Parameter('user_id1', 1), * new Parameter('user_id2', 2) ))); * </code> * * Notice: This method overrides ALL parameters in Doctrine 2.3 and up. * We keep the old Doctrine < 2.3 behavior here for Shopware BC reasons, * however this will change in the future. Use {@link setParameter()} * instead or call {@link setParameters()} only once, or with all the * parameters. * * @param \Doctrine\Common\Collections\ArrayCollection|array $parameters The query parameters to set. * @return QueryBuilder This QueryBuilder instance. */ public function setParameters($parameters) { $existingParameters = $this->getParameters(); if (count($existingParameters) && is_array($parameters)) { return $this->addParameters($parameters); } return parent::setParameters($parameters); }
/** * We update the query to count, get ids and fetch data, so, we can lost expected query builder parameters, * and we have to remove them * * @param QueryBuilder $qb */ public static function removeExtraParameters(QueryBuilder $qb) { $parameters = $qb->getParameters(); $dql = $qb->getDQL(); foreach ($parameters as $parameter) { if (strpos($dql, ':' . $parameter->getName()) === false) { $parameters->removeElement($parameter); } } $qb->setParameters($parameters); }
/** * @param QueryBuilder $qb * @param Criteria $criteria */ protected function buildWhereClause(QueryBuilder $qb, Criteria $criteria) { $values = array(); foreach ($criteria as $key => $value) { if ($this->metadata->hasField($key) || $this->metadata->hasAssociation($key)) { $qb->andWhere('e.' . $key . ' = :' . $key); $values[$key] = $value; } } $qb->setParameters($values); }
/** * Build a filter query. * * @param \Symfony\Component\Form\Form $form * @param \Doctrine\ORM\QueryBuilder $query * @return \Doctrine\ORM\QueryBuilder */ public function buildQuery(Form $form, BaseQueryBuilder $query) { $this->query = $query; //$this->getPlatfornName(); $group_child = $this->groupChild($form); foreach ($group_child as $field => $child) { if ($condition = $this->applyFilter($child, $field)) { $query->andWhere($condition); } } return $query->setParameters($this->parameters); }
/** * Removes unused parameters from query builder * * @param QueryBuilder $qb */ public function fixUnusedParameters(QueryBuilder $qb) { $dql = $qb->getDQL(); $usedParameters = []; /** @var $parameter \Doctrine\ORM\Query\Parameter */ foreach ($qb->getParameters() as $parameter) { if ($this->dqlContainsParameter($dql, $parameter->getName())) { $usedParameters[$parameter->getName()] = $parameter->getValue(); } } $qb->setParameters($usedParameters); }
/** * Recreate query builder and set state again. * * @return void */ public function __wakeup() { if ($this->constraint !== NULL) { $this->queryBuilder->where($this->constraint); } if (is_array($this->orderings)) { foreach ($this->orderings as $propertyName => $order) { $this->queryBuilder->addOrderBy($this->queryBuilder->getRootAlias() . '.' . $propertyName, $order); } } if (is_array($this->joins)) { foreach ($this->joins as $joinAlias => $join) { $this->queryBuilder->leftJoin($join, $joinAlias); } } $this->queryBuilder->setFirstResult($this->offset); $this->queryBuilder->setMaxResults($this->limit); $this->queryBuilder->setParameters($this->parameters); unset($this->parameters); }
/** * Applies mass action parameters on the query builder * * @param QueryBuilder $qb * @param bool $inset * @param array $values */ public function applyMassActionParameters($qb, $inset, $values) { if (!empty($values)) { $valueWhereCondition = $inset ? $qb->expr()->in($this->getAlias(), $values) : $qb->expr()->notIn($this->getAlias(), $values); $qb->andWhere($valueWhereCondition); } if (null !== $qb->getDQLPart('where')) { $whereParts = $qb->getDQLPart('where')->getParts(); $qb->resetDQLPart('where'); foreach ($whereParts as $part) { if (!is_string($part) || !strpos($part, 'entityIds')) { $qb->andWhere($part); } } } $qb->setParameters($qb->getParameters()->filter(function ($parameter) { return $parameter->getName() !== 'entityIds'; })); // remove limit of the query $qb->setMaxResults(null); }
public function filter(QueryBuilder $queryBuilder, $metadata, $option) { if (isset($option['where'])) { if ($option['where'] === 'and') { $queryType = 'andWhere'; } elseif ($option['where'] === 'or') { $queryType = 'orWhere'; } } if (!isset($queryType)) { $queryType = 'andWhere'; } $orX = $queryBuilder->expr()->orX(); $em = $queryBuilder->getEntityManager(); $qb = $em->createQueryBuilder(); foreach ($option['conditions'] as $condition) { $filter = $this->getFilterManager()->get(strtolower($condition['type']), array($this->getFilterManager())); $filter->filter($qb, $metadata, $condition); } $dqlParts = $qb->getDqlParts(); $orX->addMultiple($dqlParts['where']->getParts()); $queryBuilder->setParameters(new ArrayCollection(array_merge_recursive($queryBuilder->getParameters()->toArray(), $qb->getParameters()->toArray()))); $queryBuilder->{$queryType}($orX); }
/** * prepareCndCommon * * @param \Doctrine\ORM\QueryBuilder $queryBuilder QueryBuilder instance * @param string $value Searchable value * @param string $fieldName Searchable parameter name * @param boolean $exactCmp Flag: use exact comparison (=) or 'LIKE' OPTIONAL * @param string $alias Profile entity alias OPTIONAL * * @return void */ protected function prepareCndCommon(\Doctrine\ORM\QueryBuilder $queryBuilder, $value, $fieldName, $exactCmp = true, $alias = 'p') { if (!empty($value)) { $queryBuilder->andWhere(sprintf('%s.%s %s', $alias, $fieldName, $exactCmp ? '= :' . $fieldName : 'LIKE :' . $fieldName)); $queryBuilder->setParameters(array_merge($queryBuilder->getParameters(), array($fieldName => $exactCmp ? $value : '%' . $value . '%'))); } }
/** * * @access public * @param \Doctrine\ORM\QueryBuilder $qb * @param Array $parameters * @return \Doctrine\Common\Collections\ArrayCollection */ public function all(QueryBuilder $qb, $parameters = array()) { if (count($parameters)) { $qb->setParameters($parameters); } try { return $qb->getQuery()->getResult(); } catch (\Doctrine\ORM\NoResultException $e) { return null; } }
/** * Build where statement and add to the query builder. * * @param \Doctrine\Orm\QueryBuilder $qb * @param mixed $where * @return $this */ protected function addWhere($qb, $where) { // process the $where if (is_string($where)) { // straight DQL string $qb->andWhere($where); } elseif (is_array($where) && count($where)) { // create where expression $whereExp = $qb->expr()->andx(); $params = array(); // index for the parameters $i = 0; // loop through all the clauses supplied foreach ($where as $col => $val) { if (is_array($val) && (!isset($val['value']) || is_string($val['value']) && strlen($val['value']) == 0) || is_string($val) && (!$val || strlen($val) == 0)) { // skip if invalid value; continue; } // check if we've been provided with an operator as well as a value if (!is_array($val)) { $operator = Expr\Comparison::EQ; $val = $val; } elseif (count($val) == 1) { $operator = Expr\Comparison::EQ; $val = end($val); } else { $operator = isset($val['operator']) ? $val['operator'] : Expr\Comparison::EQ; $val = array_key_exists('value', $val) ? $val['value'] : array(); } // set the alias to the default $alias = $this->alias; // if col relates to a relation i.e. Role.id // then perform a join and set up the alias and column names if (strpos($col, '.') !== false) { $parts = explode('.', $col); $col = array_pop($parts); $par = $this->alias; foreach ($parts as $rel) { $alias = strtolower($rel); $jt = new Expr\Join(Expr\Join::LEFT_JOIN, $par . '.' . $rel, $alias); if (!strpos($qb->getDql(), $jt->__toString()) !== false) { $qb->leftJoin($par . '.' . $rel, $alias); } $par = $alias; } } // process sets a little differently if (!is_array($val)) { $val = array($val); } if ($operator == 'regexp') { $whereExp->add("REGEXP(" . $alias . '.' . $col . ",'" . $val[0] . "') = 1"); } else { if ($operator == 'between') { if (count($val) == 2) { // $value should now be an array with 2 values $expr = new Expr(); $from = is_int($val[0]) ? $val[0] : "'" . $val[0] . "'"; $to = is_int($val[1]) ? $val[1] : "'" . $val[1] . "'"; $stmt = $expr->between($alias . '.' . $col, $from, $to); $whereExp->add($stmt); } } else { if ($operator == 'is') { $expr = new Expr(); $method = 'is' . ucfirst($val[0]); if (method_exists($expr, $method)) { $stmt = $expr->{$method}($alias . '.' . $col); $whereExp->add($stmt); } } else { // this holds the subquery for this field, each component being an OR $subWhereExp = $qb->expr()->orX(); foreach ($val as $value) { if ($value == null) { $cmpValue = 'NULL'; } else { $cmpValue = '?' . $i; // wrap LIKE values if ($operator == 'like') { $value = '%' . trim($value, '%') . '%'; } // add the parameter value into the parameters stack $params[$i] = $value; $i++; } $comparison = new Expr\Comparison($alias . '.' . $col, $operator, $cmpValue); $subWhereExp->add($comparison); } // add in the subquery as an AND $whereExp->add($subWhereExp); } } } } // only add where expression if actually has parts if (count($whereExp->getParts())) { $qb->where($whereExp); } // set the params from the where clause above $qb->setParameters($params); } return $this; }
/** * Build IN () condition * * @param \Doctrine\ORM\QueryBuilder $qb Query builder * @param array $data Hash array * @param string $prefix Placeholder prefix OPTIONAL * * @return array Keys for IN () function */ public static function buildInCondition(\Doctrine\ORM\QueryBuilder $qb, array $data, $prefix = 'arr') { list($keys, $data) = static::prepareArray($data, $prefix); $qb->setParameters($data); return $keys; }
public function addParameters(QueryBuilder &$queryBuilder) { $queryBuilder->setParameters(array($this->getParameters()[0] => $this->getValue()[0], $this->getParameters()[1] => $this->getValue()[1])); }
/** * @param QueryBuilder $queryBuilder * @return void */ public function __invoke(QueryBuilder $queryBuilder) { $options = $this->getOptions(); if (!$options->getCriteria()) { return null; } $fieldMapping = $options->getFieldMapping(); $valueTypeMapping = $options->getValueTypeMapping(); $expressionBuilder = new ExpressionBuilder(); $parameters = []; foreach ($options->getCriteria() as $field => $value) { if ($value != '') { $queryField = $field; if (array_key_exists($field, $fieldMapping)) { $queryField = $fieldMapping[$field]; } $method = 'eq'; if (array_key_exists($field, $valueTypeMapping)) { $method = $valueTypeMapping[$field]; } if (is_array($method)) { $value = explode($method[1], $value); $method = $method[0]; } switch ($method) { case self::LIKE_LEFT: $value = '%' . $value; break; case self::LIKE: $value = '%' . $value . '%'; break; case self::LIKE_RIGHT: $value .= '%'; break; } if ($method === self::FIELD_TO_FIELD) { $queryBuilder->andWhere($expressionBuilder->eq($queryField, $value)); } else { if (method_exists($expressionBuilder, $method)) { if (is_array($value) && $method !== self::IN && $method !== self::NOT_IN) { call_user_func_array([$expressionBuilder, $method], $value); } else { $parameterNumber = $expressionBuilder->getNextParameterNumber(); $parameters[$parameterNumber] = $value; $queryBuilder->andWhere($expressionBuilder->{$method}($queryField, '?' . $parameterNumber)); } } } } } $queryBuilder->setParameters($parameters); }
/** * Set Parameters to Query */ protected function setParametersToQuery() { $this->qb->setParameters($this->parametersMap); }
/** * Convert a <tt>Predicate</tt> to a <tt>QueryBuilder</tt> * * @param \Zend\Db\Sql\Predicate\PredicateSet|PredicateInterface $predicateSet * @param QueryBuilder $queryBuilder * @param array $aliases * @return QueryBuilder */ private function transformPredicate(PredicateInterface $predicateSet, QueryBuilder $queryBuilder, $aliases) { foreach ($predicateSet->getPredicates() as $value) { $predicate = $value[1]; if (method_exists($predicate, 'getIdentifier')) { /** @var \Zend\Db\Sql\Predicate\Between $predicate */ if (false === strpos($identifier = $predicate->getIdentifier(), '.')) { $predicate->setIdentifier($aliases[0] . '.' . $identifier); } elseif (false !== ($format = @vsprintf($identifier, $aliases))) { $predicate->setIdentifier($format); } } switch ($type = shorten(get_class($predicate))) { case 'Predicate': // eg. nest/unnest /** @var \Zend\Db\Sql\Predicate\Predicate $predicate */ $expr = $this->transformPredicate($predicate, $this->createQueryBuilder($aliases[0]), $aliases)->getDQLPart('where'); break; case 'Between': case 'NotBetween': /** @var \Zend\Db\Sql\Predicate\Between $predicate */ $expr = sprintf($predicate->getSpecification(), $predicate->getIdentifier(), $predicate->getMinValue(), $predicate->getMaxValue()); break; case 'Expression': /** @var \Zend\Db\Sql\Predicate\Expression $predicate */ $expr = $predicate->getExpression(); $queryBuilder->setParameters($predicate->getParameters()); break; case 'In': case 'NotIn': /* @see \Doctrine\ORM\Query\Expr::in * @see \Doctrine\ORM\Query\Expr::notIn * @var \Zend\Db\Sql\Predicate\In $predicate */ $expr = $queryBuilder->expr()->{lcfirst($type)}($predicate->getIdentifier(), $predicate->getValueSet()); break; case 'IsNotNull': case 'IsNull': /* @see \Doctrine\ORM\Query\Expr::isNull * @see \Doctrine\ORM\Query\Expr::isNotNull * @var \Zend\Db\Sql\Predicate\IsNull $predicate */ $expr = $queryBuilder->expr()->{lcfirst($type)}($predicate->getIdentifier()); break; case 'Like': case 'NotLike': /* @see \Doctrine\ORM\Query\Expr::like * @see \Doctrine\ORM\Query\Expr::notLike * @var \Zend\Db\Sql\Predicate\Like $predicate */ $expr = $queryBuilder->expr()->{lcfirst($type)}($predicate->getIdentifier(), $predicate->getLike()); break; case 'Literal': /* @var \Zend\Db\Sql\Predicate\Literal $predicate */ $expr = trim($queryBuilder->expr()->literal($predicate->getLiteral())->getParts()[0], "'"); if (false !== ($format = @vsprintf($expr, $aliases))) { $expr = $format; } break; default: /* @var \Zend\Db\Sql\Predicate\Operator $predicate */ if (PredicateInterface::TYPE_IDENTIFIER === $predicate->getLeftType()) { $left = $predicate->getLeft(); $right = $predicate->getRight(); } else { $left = $predicate->getRight(); $right = $predicate->getLeft(); } if (false === strpos($left, '.')) { $left = $aliases[0] . '.' . $left; } elseif (false !== ($format = @vsprintf($left, $aliases))) { $left = $format; } $expr = new Comparison($left, $predicate->getOperator(), $right); } /* @see \Doctrine\ORM\QueryBuilder::andWhere * @see \Doctrine\ORM\QueryBuilder::orWhere * @see \Doctrine\ORM\QueryBuilder::andHaving * @see \Doctrine\ORM\QueryBuilder::orHaving */ $queryBuilder->{strtolower($value[0]) . 'Where'}($expr); } return $queryBuilder; }
/** * Sets a collection of filter criteria parameters for the query being constructed. * * <code> * $filter = $service->buildFilterCriteria('u') * ->where('u.id = :user_id1 OR u.id = :user_id2') * ->setParameters(array( * ':user_id1' => 1, * ':user_id2' => 2 * )); * </code> * * @param array $params The filter criteria parameters to set. * @param array $types array of PDO::PARAM_* or \Doctrine\DBAL\Types\Type::* constants * * @return Criteria */ public function setParameters(array $params, array $types = array()) { $this->queryBuilder->setParameters($params, $types); return $this; }
/** * set query where * * @param string $where * @param array $params * * @return Datatable */ public function setWhere($where, array $params = array()) { $this->queryBuilder->where($where); $this->queryBuilder->setParameters($params); return $this; }