PHP QueryBuilder::setParameters方法代码示例

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

//.........这里部分代码省略.........
         // 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;
 }