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;
}
